Pivot Table... well maybe?

[Roosta]

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

 

 

[Vitalikssssss]

Hi @Roosta,

Can you share a datasource table, so I could try to build a relevant example using Pivot?

Regards,

vitalikssssss

[Roosta]

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

[Vitalikssssss]

Hi @Roosta,

I have managed to create the following Pivot Report based on the data you have shared.

Let me know if you need any additional calculations in this Pivot report.

Regards,

vitalikssssss

 

a1_Pivot_test_1_0_2019-Sep-26_1403.zip

[Roosta]

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

Edited December 2, 2019 by Roosta
Terrible spelling

[Roosta]

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

 

 

 

 

 

[Vitalikssssss]

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

[Roosta]

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

[Roosta]

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

 

 

[Roosta]

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

 

 

 

 

 

[Roosta]

Solved it! (well sort of...)

To get round the issues I created a number of formula fields in my results table similar to this creating columns of 0's and 1's depending on the results which can then be summed;

CASE
WHEN [@field:AS_Audit_Sat] = 'Satisfactory' THEN 1

ELSE 0

END

Then in the pivot tables calculated field that I need to get a percentage I used;

ISNULL(SUM([@field:DEA_Main_Body_AudOut_Sat]),1)/(SUM([@field:DEA_Main_Body_AudLive_Val]))

Setting the format to percentage with the result being this;

This has also saved me from having to use Select Counts in the other calculated fields as all I need is something like (yes I know I could have set the calculated field to summarise by "Sum" but it puts "Sum of" in front of the column label text which I didn't want);

SUM([@field:DEA_Main_Body_AudOut_Sat])

All I need to do is get the conditional formatting  of the %  columns which is a bit difficult as there do not appear to be any Element ID's associated with the cells, I got the above by using Class Name and column No. using the below;

<script>
function f_color(){
if('[@calcfield:11]' == '100 %') {
document.getElementsByClassName('cbResultSetData cbResultSetTableCellNumberDate cbResultSetCalculatedField') [3].style.backgroundColor="green";
document.getElementsByClassName('cbResultSetData cbResultSetTableCellNumberDate cbResultSetCalculatedField') [3].style.color="white";
}else{document.getElementsByClassName('cbResultSetData cbResultSetTableCellNumberDate cbResultSetCalculatedField') [3].style.backgroundColor="red";
document.getElementsByClassName('cbResultSetData cbResultSetTableCellNumberDate cbResultSetCalculatedField') [3].style.color="white";
}
}
f_color();
</script>

However it isn't working for the whole column or changing color based on the value as can be seen below;

 

So as always any suggestions would be gratefully received!

Many thanks,

Roosta

 

[Vitalikssssss]

Hi @Roosta,

Can you send me an exported copy of Datapage along with dependencies or URL of the Datapage?

You can use Private messages for sharing exported file.

Regards,

vitalikssssss

[Roosta]

5 hours ago, Vitalikssssss said:

Hi @Roosta,

Can you send me an exported copy of Datapage along with dependencies or URL of the Datapage?

You can use Private messages for sharing exported file.

Regards,

vitalikssssss

Hi @Vitalikssssss

We have come up with a partial solution which I have posted here if you want to have a look it might be along the lines of what you were thinking already or maybe you could improve on it which would be appreciated 

Kind regards,

 

Roosta