Subtract Lac A values

Q&A
, ,
1

I want to subtract values of 2 calculated fields called “sales” and “mean”.

I have calculated sum using group by for “sales” as:
sum({q_requested},[{prod_id},{s_id},{seg_id},{seg_type},{c_id},{o_p_id},{prod_type},{prod_brand},{fore_date},truncDate("MM",{order_date})]),

also calculated average for a mean value “mean” as:
avg({mean_value},[{prod_id},{s_id},{seg_id},{seg_type},{c_id},{o_p_id},{prod_type},{prod_brand},{fore_date},truncDate("MM",{order_date})]).

Now i want to subtract values of these two fields:
{sales}-{mean}
to calculate a error between 2 values, but the visual provides me a error of this caliber.

“The LAC aggregate functions inside one visual aggregate function should always share the same grouping key”.
While trying to visualize in a table, with same grouping keys.

1 Like
2

Hello @mamilone, welcome to the QuickSight community!

I am uncertain of exactly why that error would appear since the grouping fields do match. My only real concern is the number of grouping fields you are utilizing and maybe you are hitting some kind of limitation. My first recommendation would be to switch to LAC-W aggregations and see if that resolves the issue you are facing:

Month Date = truncDate("MM",{order_date})

Sales = sumOver({q_requested},[{prod_id},{s_id},{seg_id},{seg_type},{c_id},{o_p_id},{prod_type},{prod_brand},{fore_date}, {Month Date}], PRE_AGG)

Mean = avgOver({mean_value},[{prod_id},{s_id},{seg_id},{seg_type},{c_id},{o_p_id},{prod_type},{prod_brand},{fore_date}, {Month Date}], PRE_AGG)

I would recommend including the full calculations in the calculated field that substracts one from the other, but if you do build them separately, you want to format them like this in the subtraction calculation:

minOver({Sales}, [{prod_id},{s_id},{seg_id},{seg_type},{c_id},{o_p_id},{prod_type},{prod_brand},{fore_date}, {Month Date}], PRE_AGG) - minOver({Mean}, [{prod_id},{s_id},{seg_id},{seg_type},{c_id},{o_p_id},{prod_type},{prod_brand},{fore_date}, {Month Date}], PRE_AGG)

This should resolve the error you are hitting, unless there is an unforeseen limitation with the number of fields in the partition brackets. Please let me know if this helps!

1 Like
3

Hello @mamilone, since we have not heard back from you with any follow-up questions, I will mark my previous response as the solution. If you need further assistance with this question, please reply with some more information about the errors you are facing, as well as alternate options you have tried, and I can help guide you further. Thank you!

1 Like