Not able to use 2 level of aggregations with same set of grouping columns

Question & Answer
1

Hi All,

Is there way to achieve using 2 LAC functionality with other QS functions . I have a usecase to do sum(avg(col,grp by 1,2,3),grp by 1,2,3)

code :
Sum(avg((ifelse(
${Currency} = “EUR”,{eur_rate},
${Currency} = “USD”,{usd_rate},
${Currency} = “LocalMP”,1
,0)
*
ifelse(
${BpsBY} = “PCcogs”,{p_cogs_cy},
${BpsBY} = “PRev”,{p_rev_cy},
${BpsBY} = “NetRev”,{net_rev_cy}
,0)),[period,{gl_product_group},{week_der},{brand_code},{vendor_code},{vendor_company_code}]),[period,{gl_product_group},{week_der},{brand_code},{vendor_code},{vendor_company_code}])

It errored out
Visual agg functions can’t be used operands of as Level Aware Calculation (LAC) agg functions

Thanks,
Pavan

2

Hi @PavanKanna

try to create the nested aggregation as a separate calculated field at the DATA SET PREP level, not in the analysis.

Hope it helps,
GL

3

Thanks for responding i’m getting weird issue at dataset level not able to

avg(p_rev_cy,[{period}])

Function AVG should have 1 argument(s) instead of 2 argument(s).

4

try

avgOver( sum({p_rev_cy}), [{period}], PRE-AGG)

FYI:
calculation level

(Optional) Specifies the calculation level to use:

    PRE_FILTER – Prefilter calculations are computed before the dataset filters.

    PRE_AGG – Preaggregate calculations are computed before applying aggregations and top and bottom N filters to the visuals.

    POST_AGG_FILTER – (Default) Table calculations are computed when the visuals display.
1 Like