Jump to content
  • 0

Use Totals & Aggregations Values In Calculated Fields And Graphs


Question

Example:

 

Record 1 - Field1='1' Field2='1' Field3='BLANK' Field4='1'

Record 2 - Field1='0' Field2='1' Field3='0' Field4='1'

Record 3 - Field1='1' Field2='1' Field3='1' Field4='1'

 

What I need to do is SUM all of the 4 fields and COUNT non-blanks for all four fields and then divide the SUM by the COUNT, and do this across Records.

 

In this example, the way I need it to work would be that the SUM=9 and the COUNT=11 for total calculated average of 81.81%.

 

In Caspio, I can create the two calculated fields to SUM and COUNT for each record. Then I can run an aggregation to SUM each calculated field to get the numbers 9 and 11.

 

However, I cannot create a second calculation to create SUM/COUNT using the aggregated values.

 

If I create a single calculation for SUM/COUNT it does this for each individual record. I can then graph that field and AVERAGE them together but it will calculate in the following manner:

 

Record 1 = 100%

Record 2 = 50%

Record 3 = 100%

Calculated Average = 83.33%

 

Is there a way to use the aggregated values in any manner?

Link to post
Share on other sites

21 answers to this question

Recommended Posts

  • 0

Hi...

 

It can be done with calculated fields. To sum all field, you can use the following calculated field:

(IsNull([@field:Field1],0)+IsNull([@field:Field2],0)+IsNull([@field:Field3],0)+IsNull([@field:Field4],0))

 

To count not blank value, you can use the following calculated field:

case when [@field:field1] IS NOT NULL then 1 else 0 end +
case when [@field:field2] IS NOT NULL then 1 else 0 end +
case when [@field:field3] IS NOT NULL then 1 else 0 end  + case when [@field:field4] IS NOT NULL then 1 else 0 end

 

Now you can just use:

[@calcfield:1]/[@calcfield:2]

 

If you don't want to create 3 calculated fields and just want to display everything in one, you can use the following formula:

 

(IsNull([@field:Field1],0)+IsNull([@field:Field2],0)+IsNull([@field:Field3],0)+IsNull([@field:Field4],0))/cast((case when [@field:field1] IS NOT NULL then 1 else 0 end +
case when [@field:field2] IS NOT NULL then 1 else 0 end +
case when [@field:field3] IS NOT NULL then 1 else 0 end  + case when [@field:field4] IS NOT NULL then 1 else 0 end) as decimal)

 

You need to convert divisor to decimal to get correct results, or you can just multiply devisor by 1.0 instead of using cust. It will also give you correct answer:

 

(IsNull([@field:Field1],0)+IsNull([@field:Field2],0)+IsNull([@field:Field3],0)+IsNull([@field:Field4],0))/((case when [@field:field1] IS NOT NULL then 1 else 0 end +
case when [@field:field2] IS NOT NULL then 1 else 0 end +
case when [@field:field3] IS NOT NULL then 1 else 0 end  + case when [@field:field4] IS NOT NULL then 1 else 0 end)*1.0)

 

Hope this helps.

Link to post
Share on other sites
  • 0

Seems that I misunderstood the problem. Here is an another solution :) Hope, this one will work for you.

 

You can use SQL statements in the calculated field to sum/count values thought all records, but in this case, you will have the same value for calculated field in all records. Here is the formula:

select (sum(field1) + sum(field2) + sum(field3) + sum(field4))/(count(field1) +  count(field2) + count(field3) + count(field4)) from my_table

 

Where field1, field2, ... field4 are fields from your table, and my_table is a table name.

If your DataPage has any search criteria you should mention them in calculated field after where, here is an example:

 

select (sum(field1) + sum(field2) + sum(field3) + sum(field4))/(count(field1) +  count(field2) + count(field3) + count(field4)) from my_table where field1>[@a] AND field2 < [@c]

Link to post
Share on other sites
  • 0

Here is what I have so far and it is returning Error in Formula

 

select
 
[@field:PreCall_Supervisor], [@field:PreCall_SupervisorLocation], [@field:PreCall_AC], [@field:Date], (sum([@field:Listening_Listen])+sum([@field:Listening_Concerns])+sum([@field:Listening_Probe])+sum([@field:Listening_Paraphrase]))/(count([@field:Listening_Listen])+count([@field:Listening_Concerns])+count([@field:Listening_Probe])+count([@field:Listening_Paraphrase]))
 
from RA_Received
 
where [@field:Date]>[@BeginDate] AND [@field:Date]<[@EndDate] AND [@field:PreCall_SupervisorLocation]=[@Location] AND [@field:PreCall_Supervisor]=[@Supervisor] AND [@field:PreCall_AC]=[@AC]
Link to post
Share on other sites
  • 0

Use field name with select, not field parameter. Your calculated field will look like this:

 
select
(sum(Listening_Listen)+sum(Listening_Concerns)+sum(Listening_Probe)+sum(Listening_Paraphrase))/(count(Listening_Listen)+count(Listening_Concerns)+count(Listening_Probe)+count(Listening_Paraphrase))
 
from RA_Received
 
where Date>[@BeginDate] AND Date<[@EndDate] AND PreCall_SupervisorLocation=[@Location] AND PreCall_Supervisor=[@Supervisor] AND PreCall_AC=[@AC]
Link to post
Share on other sites
  • 0

The solution provided above calculates the average properly however since it uses calculated field the average is displayed on each record which I don't think is desired. Here is another approach:

 

1) Add two Calculated Fields: Sum and Count

2) In the Sum Calculated Field enter the formula:

IsNull([@field:field1],0)+IsNull([@field:field2],0)+ ... +IsNull([@field:fieldn],0)

3) In the Count Calculated Field enter the formula:

(IsNumeric([@field:field1])+IsNumeric([@field:field2])+ ... +IsNumeric([@field:fieldn]))

This formula allows you to calculate the number of non blank values.

4) Add a Totals & Aggregation element, select both Calculated Fields: Sum and Count, use the function SUM.

5) Add the Header & Footer element and in the Footer enter the JavaScript code below:

<SCRIPT LANGUAGE="JavaScript">
var sum = document.getElementsByClassName("cbResultSetTotalsData cbResultSetTotalsDataCellNumberDate")[0].innerHTML;
var number = document.getElementsByClassName("cbResultSetTotalsData cbResultSetTotalsDataCellNumberDate")[1].innerHTML;
var avg=sum/number;
document.getElementsByClassName("cbResultSetTotalsData cbResultSetTotalsDataCellNumberDate")[0].innerHTML=avg.toFixed(2);
document.getElementsByClassName("cbResultSetTotalsData cbResultSetTotalsDataCellNumberDate")[1].innerHTML="";
</SCRIPT>

Notes:

 

  • The JavaScript does the final calculation and write the final value into the aggregation row and blank out the count cell.
  • avg.toFixed(2) rounds the number to 2 numbers after decimal point. You can adjust it as desired.
  • document.getElementsByClassName("cbResultSetTotalsData cbResultSetTotalsDataCellNumberDate")[0] ... 0 refers to the first data cell in totals and aggregation row and 1 refers to the second data cell and so on...
  • Use F12 in browser to open developer tool where you can inspect page elements and find their id, class, ....to adjust your scripts when needed.
Link to post
Share on other sites
  • 0

I agree that the final calculation being displayed for each record is not desired, but I do need to graph the final %. With the javascript solution I do not see how I can graph the value.

 

I am still getting an error in the other formula because of the WHERE function.

 

where Date>[@BeginDate] AND Date<[@EndDate] AND PreCall_SupervisorLocation=[@Location] AND PreCall_Supervisor=[@Supervisor] AND PreCall_AC=[@AC]

 

Date, PreCall_SupervisorLocation, etc. are my table field names, and @BeginDate, @EndDate are my parameters passed from the search form.

 

When I remove the WHERE function the calculation works but obviously not for the defined search criteria.

Link to post
Share on other sites
  • 0

Please, put your parameters in single quotes in order to make your criteria working, and add N infront, if there can be non alphabetical characters in your parameters. So it will look like:

where Date>=N'[@BeginDate]' AND Date<=N'[@EndDate]'AND PreCall_SupervisorLocation=N'[@Location]' AND PreCall_Supervisor=N'[@Supervisor]' AND PreCall_AC=N'[@AC]'

 

But in the formula above all criteria are required, otherwise you need to use case statement which will make your calculated field more complected.

And one more comment. To sum all your fields correctly (ignoring blank values), you need to use IsNull function. Like this:

(sum(IsNull(Listening_Listen),0)+sum(IsNull(Listening_Concerns),0)+sum(IsNull(Listening_Probe),0)+sum(IsNull(Listening_Paraphrase),0)

Link to post
Share on other sites
  • 0

Thank you to everyone for your assistance. I have gotten the calculation work as expected. Here is my final formula:
 
select
 
(sum(IsNull(Listening_Listen,0))+sum(IsNull(Listening_Concerns,0))+sum(IsNull(Listening_Probe,0))+sum(IsNull(Listening_Paraphrase,0)))/(count(Listening_Listen)+count(Listening_Concerns)+count(Listening_Probe)+count(Listening_Paraphrase))
 
from RA_Received
 
where Date>=N'[@BeginDate]' AND Date<=N'[@EndDate]' AND PreCall_SupervisorLocation='[@Location]' AND PreCall_Supervisor='[@Supervisor]' AND PreCall_AC='[@AC]'
 
HOWEVER,
 
when I try to graph my only option is to display a bar for each record that is queried. It will not let me use an Average calculation in the graph to get one bar on the graph. When Average or any other "Aggregation" on the graph is selected it returns an Error in Formula.

Link to post
Share on other sites
  • 0

I have a tabular report with columns Store and Score. I need to create an average score for each store, where the table Audit_Master_Database would have several records for the same store. I have placed the following in a calculated field however it is creating the same average for all stores listed. How do I have the script create the average score from Audit_Master_Database but only from the tabular report's row Store?

 

 

select 
 
(avg(Score))
 
from Audit_Master_Database
Link to post
Share on other sites
  • 0

Can I also replace the SQL lookup value that calculates count below with the value of the current tabular report row (called Q) that has same lookup value as Q1A? This is what I tried but I receive a calculated error:

 

select count(target.[@field:Q]) from Audit_Master_Database where Format='[@Format]' AND AuditType='[@AuditType]' AND target.[@field:Q] = '[@Y]'

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...