Level Aware Calculation (LAC) and Pivot Table

[jesstnto]

Hi, I want to mimic an excel calculation in a pivot table.

I want to calculate the sum of Target and MAX GMS USD (both on warehouse level) on type short level, but i can’t simply use MAX(Target) * MAX GMS USD , where MAX GMS USD = max({gms_usd}, [{warehouse_id}, {week_of_the_year}]) because if i don’t put warehouse in the pivot table, it will take the max of type group instead of sum of (target x gms) and make wrong calculation.

I want my final pivot to be on type level aggregation.

I tried to use max(Target, [{week_of_the_year},{country_key},{Type Short},{warehouse_id}])*{MAX GMS USD Type} (additional partition by type short and country key), and i can save it, but whenever i try to put it into a visual it will return

Please kindly help, thank you!

[David_Wong]

Hi @jesstnto,

Do you mean that you want to calculate max(Target) * max(gsm_usd) for each warehouse and then calculate the sum for all warehouses that have the same type?

[jesstnto]

Hi @David_Wong ,
Yes, i want to sum it per country, per type. But I don’t want to put warehouse granularity in the visualization.

[David_Wong]

Hi @jesstnto,

Can you try this? If you don’t put warehouse_id in your calculated field, I think this should work.

sum(max({Target}, [{warehouse_id}]) * max({gsm_usd}, [{warehouse_id}]))

[jesstnto]

Hi David, that works! Thanks a lot!!!