Add Calculated Totals to a Sub-Query Field with an Advanced Function applied

Zack Wilson shared this idea 35 days ago
Idea Logged

My biggest issue is I am having a hard time understanding why the "Calculated Total" does not work on the Advanced Function.

I will try to explain what I mean. Also to note... I found the way around it (which didn't work in other versions), but it seems unnecessary.

1) Subqueries for Prior YTD and Current YTD:

/df2a4f38f1913468c85d3163720b18b5


2) Calculated Field for Difference in 2 subquery calcs (on main query):

/c0205b71725fd7409d0ac5b6f37a94a9


3) CF on table and attempt at Advanced Function (from main query):

/cb239a6092ef591d6328cf9395a30cd0


4) Tried to use Sub-Query Field from Current YTD:

/22d2e3f1b7dbc7a9c6dc1200e62974a1

5) Work-around:

/a58fd3ab2c63736a7b3a39c5bbea7cf7


I hope this makes sense. I may be making a big deal out of nothing, but I wasn't sure if this "work-around" was intended...

Comments (1)

photo
1

Hi Zack,

Thanks for reaching out. This discrepancy comes down to some order of operations issue, with how the totals are calculated. Some functions in YF are done as SQL queries, other calculations are done in memory. From a possibly related developer conversation:

"I confirmed that you cannot have aggregation on fields that are already being aggregated as part of the calculated field, since the calculated field aggregation happens at the SQL query level. Since aggregation is not supported, whenever you turn on the section totals it will default to a 'None' aggregation for the total function, which means it will appear blank. This is because the totals are by default set to the same aggregation that the field has (eg. Sum Inv. amount will have a default aggregation of Sum, Avg Age at camp will have Average). Once you change the total function aggregation to 'Sum' or any other total, they will start appearing, but they will be performed on each individual result of the calc function, which can be different the aggregation of the all the values coming from the raw dataset."

Considering this is currently expected behavior, I've gone ahead and logged an enhancement request for this.

Any potential updates surrounding this will be posted here.

Regards,

Mike