Jump to content
  • 0
Roosta

Pivot Table... well maybe?

Question

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;

image.thumb.png.69750364eb3143f6573c795ce451b989.png

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

 

 

Share this post


Link to post
Share on other sites

9 answers to this question

Recommended Posts

  • 0

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

Share this post


Link to post
Share on other sites
  • 0

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;

image.thumb.png.9f68bd533a9f47039ddf2c8c1095aa53.png

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

 

 

 

 

 

Share this post


Link to post
Share on other sites
  • 0

Hi @Roosta,

I assume that you see "Error in Formula" because one of the divisor results in zero.

You need to add CASE check in order to avoid division by zero.

It can look like this:

([@calcfield:1]/

(CASE THEN [@calcfield:9] = 0 THEN 1
 ELSE [@calcfield:9]
 END)

)*100

Hope this helps.

Regards,

vitalikssssss

Share this post


Link to post
Share on other sites
  • 0

Hi @Vitalikssssss

Thanks for the suggestion, I have tried as you suggested and when verifying the formula I get the following error;

image.png.fc67753228903708f2b9c556e283e07f.png

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;

image.thumb.png.51abc18920819534fe051aa0e5f66bc4.png

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

Share this post


Link to post
Share on other sites
  • 0

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

 

 

Share this post


Link to post
Share on other sites
  • 0

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;

image.thumb.png.9ff046fc7a8dccfb1b1cd11e4e5fa03f.png

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

 

 

 

 

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...