Aggr Expression is not getting picked up in Pivot Table

[deepakjp]

I have a pivot table with 3 dimensions and a measure 

Category and KPI are inline loaded and the table is pivoted by customer name.

In the measure, I have given a pick, match expression to pick up the corresponding expression for the KPI.

But when there is an AGGR expression, the value is not getting reflected in the table.

AGGR expression is working in case of Volume, which is the first row of the table. But for any other rows, it is not getting picked up.

I have only one aggr expression and I need to show it towards the middle of the table.

Please see the expression below,

pick(
match(SubCategory,
'Volume (m3)',
'ASP ($/m3)',
'Gross Revenue ($)',
'CM2 ($/m3)',
'Returned Concrete Margin ($)',
'Wait Time Margin ($)',

'Cancelled Orders (#)',

'Weighted Avg Delivery Time Slot',

),

Num(sum(quantity),'###,##0'),
Num(SUM(sales_net_value)/sum(quantity),'###,##0.00'),
Num(SUM(sales_net_value),'###,##0'),
Num(sum(CM2)/sum(quantity),'###,##0.00'),
Num(sum({<material={'GCONCRET'}>}sales_net_value) - sum({<material={'GCONCRET'}>}cost) - sum({<material={'GCONCRET'}>}freight_net_value),'###,##0'),
Num(sum({<level_6_description={'Waiting Time'}>}sales_net_value) - sum({<level_6_description={'Waiting Time'}>}cost) - sum({<level_6_description={'Waiting Time'}>}freight_net_value),'###,##0'),

Num(Sum(aggr(Avg(orders_cancelled),ship_to_info, Year,YearMonth)),'###,##0')

time(dt),

)

Please suggest if there is any workaround to make this work.

[Aurelien_Martinez · Accepted solution]

Hi,

You can try to add SubCategory in the aggr

Num(Sum(aggr(Avg(orders_cancelled),ship_to_info, Year,YearMonth, SubCategory)),'###,##0')

Aurélien

[deepakjp]

Hi Aurélien,

This solves the issue.

Thank you so much for your help.