Jump to content

Roosta

Members
  • Content Count

    7
  • Joined

  • Last visited

  1. Hello all, Well I am getting nowhere with this, I just don't get the syntax used in Pivot Table Formula FIelds I can do this (Count + Count); IsNull((SELECT COUNT([@field:DEA_Main_Body_RecordID]) from _v_DEA_Eng_Pivot_AudSatPer Where CUS_Contrator_Engs_Full_Name = target.[@field:CUS_Contrator_Engs_Full_Name]),1) +(SELECT COUNT([@field:DEA_Main_Body_RecordID]) from _v_DEA_Eng_Pivot Where CUS_Contrator_Engs_Full_Name = target.[@field:CUS_Contrator_Engs_Full_Name]) Which avoids a Zero in the first Count (The second count will always have a result of >0) But I can't do this (Count / Count): IsNull((SELECT COUNT([@field:DEA_Main_Body_RecordID]) from _v_DEA_Eng_Pivot_AudSatPer Where CUS_Contrator_Engs_Full_Name = target.[@field:CUS_Contrator_Engs_Full_Name]),1) /(SELECT COUNT([@field:DEA_Main_Body_RecordID]) from _v_DEA_Eng_Pivot Where CUS_Contrator_Engs_Full_Name = target.[@field:CUS_Contrator_Engs_Full_Name]) As it returns an "Invalid Formula" when verifying. I can't get the [@calcfield:X] use to perform in any rational manner either for example SUM([@calcfield:9]+3) seems to result in the Calc Field value added together 3 times so if its value = 1 then its 1+1+1 =3 not 1+3=4 or if you try to use [@calcfield:X]+[@calcfield:X] within a calculated field such as below; It verifies as a correct formula but returns "Error in formula" when trying to preview. There is very little information in Caspios help files for Pivot Table Calculated Field Formulas and they don't seem to behave the same way as tabular reports and the syntax definitely isn't the same as standard SQL. Any help or guidance towards some documentation for correct syntax would be gratefully received, failing that I think my only option may be to create an HTML Table in a form datapage using HTML blocks and figuring out how to display the required data. Thanks all, Roosta
  2. Hi all, After a little experimentation I am pretty sure its not recognising the Calculated Fields within the formula as an experiment I tried [@calcfield:9]+10 which just gave me a column all with the value "10" but should have been various values such as 12, 11, 13, etc when I tried ([@calcfield:9])+10 it verified as a correct formula but then displayed "Error in formula" when previewing. Maybe the Pivot Table Wizard doesn't like referencing Calculated Fields within another Calculated Fields formula? Kind regards, Roosta
  3. Hi @Vitalikssssss Thanks for the suggestion, I have tried as you suggested and when verifying the formula I get the following error; I have tried using a suggested solution from the Caspio help; Case When [@calcfield:9]= '0' Then null Else ([@calcfield:1]/[@calcfield:9])*100 End This verifies as a correct formula but when previewing shows; I will keep trying some variations on the formula to see if I can find the problem in the syntax, any suggestions more than welcome and thanks for your help so far much appreciated. Kind regards, Roosta
  4. Hello Vitalikssssss, I have only just swung back round to developing this datapage and greatly I appreciate the solution you offered but its not what I am hoping to achieve. As you can see from my orginal screen shot of an exisiting pivot table I am using as a model I have several results/fields I need to total and provide percentages for but the Caspio standard features get very confusing for users to interpret/read (the layout isn't very good even with tweaking). I have created something near what is required using calculated fields only (no columns selected and just the engineer as a row) as below; The problems that remain are that I can't figure out how to display/calculate percentages based on 2 calculated fields say Audit Total and Satisfactory (the first 2 columns) for example; I would like to carry out a calculation using the results from 2 of the calculated fields e.g. Calc Field 1 (third column) / Calc Field 9 (second column) * 100 so for example Steve Rogers would be (3/5)*100 = 60% is this possible? I have tried using ([@calcfield:1]/[@calcfield:9])*100 which verifies as a correct formula but I get an error message "Error in formula." when trying to preview it. Or I need a formula that will allow me to divide the result of this formula; IsNull((SELECT COUNT([@field:DEA_Main_Body_RecordID]) from _v_DEA_Eng_Pivot Where DEA_Main_Body_AS_Audit_Sat = "Satisfactory" AND CUS_Contrator_Engs_Full_Name = target.[@field:CUS_Contrator_Engs_Full_Name]),0) With the result of this formula; IsNull((SELECT COUNT([@field:DEA_Main_Body_RecordID]) from _v_DEA_Eng_Pivot Where CUS_Contrator_Engs_Full_Name = target.[@field:CUS_Contrator_Engs_Full_Name]),0) Then multiply by 100 to get the percentage? And as I am using calculated fields I would also need to be able to filter/search by date (have the date search fields in place) but currently they don't work as all values shown are drawn directly from the View. I also need to include conditional formating as in the orginal screenshot for the percentage fields e.g. 100% = Green <100% = Red. Hopefully I am explaining this correctly but I am coming to the conclusion that this may not be possible using the standard Pivot Table function within Caspio and may need to look at doing this in a different way if anyone has any suggestions? Kind regards, Roosta
  5. Hello Vitalikssssss, Thank you for the example it is very much appreciated I will have a look through later and let you know how I go Kind regards, Roosta
  6. Hello Vitalikssssss, I have attached a couple of truncated tables with the relevant data included, the CUS_Contractor_Engs has the engineers names and ID Field which is used in a one to many relationship with the DEA_Main_Body table. There is one column with Yes/No values and one with Satisfactory/Unsatisfactory as samples as mentioned in my original post, thanks for looking and I would be very grateful for any suggestions or guidance. Kind regards, Roosta CUS_Contrator_Engs.xlsx DEA_Main_Body.xlsx
  7. Hello all, Thanks for taking the time to have a look at my first post! I have been using Caspio for a few months now and generally it seems very versatile but I have come up against something that I can't seem to resolve. I would like to achieve something similar to below; This is version of a pivot table which counts instances of Yes/No or Satisfactory/Unsatisfactory in a single column and sums the totals in the appropriate fields then calculates the percentage based on the total number of audits undetaken for an individual (this is test not live data hence the lack of overall data and names) I have tried various ways to do this in the standard pivot table available in Caspio but don't seem to be getting anywhere and think I may need to create an HTML table of my own with calculated fields but thought I would post here to see if anyone has any ideas or has done something similar in the past? Cheers all, John
×
×
  • Create New...