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

Zack Wilson shared this idea 20 months 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 (3)

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

photo
1

Hello,

I see David, you have been given this one... Are there any updates on this. I am having a real issue with not getting the results I need.


Thanks,

Zack

photo
1

Hi Zack, Unfortunately the task is yet to be put on the roadmap so no plans to implement this as yet.


The only thing I can do to help here is figure out if there is another way around this (as I believe there is, just unsure if it will meet your needs). The only way I can get this info is to really understand the data/need.


Is the workaround listed in no.5 no longer applicable?

Are there some details that may have changed? If we will be discussing data sets that should not be on a public topic, feel free to raise a support ticket and address myself so I can pickup, and we can take it from there.


Thanks,

David

photo
1

David,


I am not sure what is going on... On a separate report, I am trying to divide a sum by a count and it only shows either 0% or 100% (see below)

Calculated Field:

/94c777ed521127b7eb03c3506e65afed


Result as Percentage:

/7df8ad08f88baef72d8fce26ac26d864

Left is the result of the calculated field (which gives me the correct total, but the wrong row %). Right is the result of using the advanced function (which gives me the correct row %, but the wrong, or no, total).


Thanks,

Zack

photo
photo
1

Hi Zack,


As covered over a call, we have worked around this issue by having the calc field return a float rather than an integer by simply adding a x 1.00 to the calculations.

This removed the need for the analytic function, and getting you the correct results you were after.


Thanks,

David