Combo chart expression not aggregating/filtering at desired level.

[Voyager_RVA]

I have a KPI that calculates a winsorized weighted average of ANNUAL_INFLATION_PCT for the current fiscal year. It works correctly.

But when I use the same expression in a combo chart that has fiscal year as dimension, the FY 2026 value is different. If I filter the combo chart to FY 2026, the values match.

AI is telling me that Qlik is aggregating data differently. In the combo chart, the calculation ignores the Fiscal Year when calculating the to get the correct values consistently, i should pre-calculate the winsor low and high values during load. causing a lot of pre-calculating based on ops_components, suppliers, item groups, etc.

Any suggestions for fixing this measure expression for a combo chart (other than removing the fiscal year filter)? or would you suggest pre-calculating during load?

My winsorization code without FY for context:

Sum(
{<OPS_COMPONENT={'X','Y','Z'}>}
RangeMin(
Fractile(
TOTAL {<OPS_COMPONENT={'X','Y','Z'}>} ANNUAL_INFLATION_PCT,
vWinsorHigh
),
RangeMax(
Fractile(
TOTAL {<OPS_COMPONENT={'X','Y','Z'}>} ANNUAL_INFLATION_PCT,
vWinsorLow
),
ANNUAL_INFLATION_PCT
)
) * TOTAL_LINE_VALUE
)
/
Sum({<OPS_COMPONENT={'X','Y','Z'}>} TOTAL_LINE_VALUE)

[hugo_andrade]

Hi @Voyager_RVA ,

I believe I understood the issue. This seems to be an effect of using TOTAL inside Fractile(). In the KPI you have one fiscal year in play, so the “TOTAL” population and the FY population are the same. In the chart, “TOTAL” makes Fractile() look across the whole chart selection (all fiscal years), so the winsor bounds (low/high) are not being calculated per fiscal year, even though the chart is displaying per fiscal year.

Try changing the Fractile() scope so it totals “within” Fiscal Year.

For example (adjust the FY field name to yours):

Numerator bounds per FY:

• Fractile( TOTAL <FISCAL_YEAR> {<OPS_COMPONENT={'X','Y','Z'}>} ANNUAL_INFLATION_PCT, $(vWinsorHigh) )

• Fractile( TOTAL <FISCAL_YEAR> {<OPS_COMPONENT={'X','Y','Z'}>} ANNUAL_INFLATION_PCT, $(vWinsorLow) )

So your measure becomes:

Sum(
{<OPS_COMPONENT={'X','Y','Z'}>}
RangeMin(
Fractile(TOTAL <FISCAL_YEAR> {<OPS_COMPONENT={'X','Y','Z'}>} ANNUAL_INFLATION_PCT, $(vWinsorHigh)),
RangeMax(
Fractile(TOTAL <FISCAL_YEAR> {<OPS_COMPONENT={'X','Y','Z'}>} ANNUAL_INFLATION_PCT, $(vWinsorLow)),
ANNUAL_INFLATION_PCT
)
) * TOTAL_LINE_VALUE
)
/ Sum({<OPS_COMPONENT={'X','Y','Z'}>} TOTAL_LINE_VALUE)

If this helps, please give it a like, mark it as Helpful or Solution if that was the case.

[Voyager_RVA]

Hello @hugo_andrade , 

you understood the issue correctly! Thank you for that as my problem description wasn't clear. I've been working in Qlik for about 6 months. 

I tried your recommendation but it's giving me a "Error in expression: Nested aggregation not allowed" message. 

Sadly - i will move on to calculating this in in a different platform and loading into Qlik Sense and abandon this approach. 

Appreciate your help. When i can figure out how Qlik Sense aggregates data, i may be able to solve this problem...

thank you