Qlik measure calculating correctly at SKU level but returning zero at higher aggregation levels (e.g., Division)?

[Diere29]

I'm working on a Qlik measure to calculate total inventory at retail (Total Inv R (K)) at the Division level. It works perfectly at the SKU level but consistently returns zero at any higher classification, like Division. Here’s the measure and its components:

Total Inv R (K) = 

ALT(AGGR(SUM([Total inv at retail]), Transform_EU_SKU_Details.Division) / 1000,0)

This measure should aggregate [Total inv at retail] at the Division level, converting it to thousands by dividing by 1,000. However, instead of summing, it just returns zero at Division and higher levels.

[Total inv at retail] = 

ALT([Transform_EU_SKU_Details.Current Unit Retail Price]*CY,0)

This calculates at SKU level, multiplying the current unit retail price by CY (see below) and defaults to 0 if null. when a division field is used to replace the SKU field it returns to zero. 

CY = 

ALT(Sum({$<IsInYTD = {1}, Metric_Days] = {
'FIFO Cost 0-30',
'FIFO Cost 31-60',
'FIFO Cost 61-90',
'FIFO Cost 91-120',
'FIFO Cost 121-180',
'FIFO Cost over 180',

'Units 0-30',
'Units 31-60',
'Units 61-90',
'Units 91-120',
'Units 121-180',
'Units over 180',

'Valued Cost 0-30',
'Valued Cost 31-60',
'Valued Cost 61-90',
'Valued Cost 91-120',
'Valued Cost 121-180',
'Valued Cost over 180'
},
Transform_QL_Calendar_454.Year = {$(=max([Transform_QL_Calendar_454.Year]))} >} [UK_IP_INVFIFO.VALUE]),0)

This calculates year-to-date values based on specific Metric_Days and Year criteria, defaulting to 0 if no value is found. Any insights on why this might be happening or how I can get it to aggregate correctly at the Division level would be greatly appreciated!

 

[Kushal_Chawda]

@Diere29  you cannot apply aggregation function over master measure. Hence, you need to apply aggregation over actual expression

=sum(aggr(Sum({$<IsInYTD = {1}, Metric_Days] = {
'FIFO Cost 0-30',
'FIFO Cost 31-60',
'FIFO Cost 61-90',
'FIFO Cost 91-120',
'FIFO Cost 121-180',
'FIFO Cost over 180',

'Units 0-30',
'Units 31-60',
'Units 61-90',
'Units 91-120',
'Units 121-180',
'Units over 180',

'Valued Cost 0-30',
'Valued Cost 31-60',
'Valued Cost 61-90',
'Valued Cost 91-120',
'Valued Cost 121-180',
'Valued Cost over 180'
},
Transform_QL_Calendar_454.Year = {$(=max([Transform_QL_Calendar_454.Year]))} >} [UK_IP_INVFIFO.VALUE]) * [Transform_EU_SKU_Details.Current Unit Retail Price],
Transform_EU_SKU_Details.Division))/1000

[rubenmarin]

Hi, the expression only does an aggr, wich may return multiple values, you need an aggreagtion function that tells what to do with all those values:

ALT(Sum(AGGR(SUM([Total inv at retail]), Transform_EU_SKU_Details.Division) / 1000),0)