Aggr Expression is not getting picked up in Pivot Table

deepakjp — Thu, 03 Aug 2023 10:01:49 GMT

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.

Re: Aggr Expression is not getting picked up in Pivot Table

Aurelien_Martinez — Thu, 03 Aug 2023 10:17:46 GMT

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

Re: Aggr Expression is not getting picked up in Pivot Table

deepakjp — Mon, 07 Aug 2023 05:10:21 GMT

Hi Aurélien,

This solves the issue.

Thank you so much for your help.

topic Re: Aggr Expression is not getting picked up in Pivot Table in App Development

Aggr Expression is not getting picked up in Pivot Table

Re: Aggr Expression is not getting picked up in Pivot Table

Re: Aggr Expression is not getting picked up in Pivot Table