APAC SD Team
Category
Report Builder
Product Version
9.0
Add Calculated Totals to a Sub-Query Field with an Advanced Function applied
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:
2) Calculated Field for Difference in 2 subquery calcs (on main query):
3) CF on table and attempt at Advanced Function (from main query):
4) Tried to use Sub-Query Field from Current YTD:
5) Work-around:
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...
I like this idea 
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
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
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
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
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
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
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
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
David,
Has there been any traction with this. Or, will this continue to be an issue?
Thanks,
Zack
David,
Has there been any traction with this. Or, will this continue to be an issue?
Thanks,
Zack
Hi Zack,
Task status on this Idea remains unresolved - you can reach out to an account manager with task ID YFN-21224 To request an escalation on this task if needed.
Thanks,
Eric
Hi Zack,
Task status on this Idea remains unresolved - you can reach out to an account manager with task ID YFN-21224 To request an escalation on this task if needed.
Thanks,
Eric
Replies have been locked on this page!