Use Totals & Aggregations Values In Calculated Fields And Graphs

[TroyPurdue]

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?

[casyana]

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.

[casyana]

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]

[TroyPurdue]

I am trying this now...

 

How do you add search criteria for a date range? Field @Date has Criteria1 and Criteria2 that are passing parameters from a search form.

[TroyPurdue]

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]

[casyana]

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]

[bahar_vm]

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.

[TroyPurdue]

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.

[caspiofan]

I have exactly the same situation, suggestion are welcome! Thanks!

[casyana]

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)

[TroyPurdue]

The WHERE function is working properly now.

 

The IsNull is returning an error saying more than one argument is necessary. The formula returns no errors when IsNull is excluded.

[MayMusic]

When using IsNull you need to have a value to replace with Null value. Change the parentheses:

sum(IsNull(Listening_Concerns),0)   => sum(IsNull(Listening_Concerns,0))

[TroyPurdue]

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.

[TroyPurdue]

Bump

[ChrisCarlson]

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

[Jan]

Hello ccarls3,

 

If I understand you correctly, you can use the following code in your Calculated field:

select avg(Score) from Audit_Master_Database where Store = target.[@field:Store]

I hope, it helps.

[ChrisCarlson]

Jan,

 

This works perfect. I can now calculate average scores in column on tabular report without having to use aggregate values. Comes in handy when using geo mapping function!

[ChrisCarlson]

see note below

[ChrisCarlson]

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]'

[iren]

Hello ccarls3,

 

You can try to use next formula:

SELECT COUNT(Q) FROM Audit_Master_Database WHERE Format=target.[@field:Format] AND AuditType=target.[@field:AuditType] AND Q = target.[@field:Y]

Does it work for you?

[ChrisCarlson]

I need to get SELECT COUNT(Q) to point to the field in the tabular report. There is no field Q in the lookup database. In the tabular report, field Q holds the matching value as the lookup database field name. So I need to use field value to step the database field name.

[munkitkat]

I have exactly the same situation, suggestion are welcome! Thanks!