Sum total aggr... help to optimize

[mike_spada]

Hi guys,

can anyone help me to optimize - if possible - this expression?

I have a pivot, dimensions are:

- supplier, bu, line, prj, year, month

- two expressions are: 1. quantity * cost and 2. % of cost regarding the sum according to dimensions.

For the first one, I'm ok.

For the second, I use this:

[Column1 ] /

Sum(TOTAL <bu, line, prj, year, month>

Aggr(Sum(if(dt_date_end=dt_month_cost_end, quantity)) * sum(distinct if(dt_month_cost=V_MAX_DT,cost_total)),supplier, bu, line, prj, year, month))

where V_MAX_DT contains the max date where cost is present, calculated in load script

and [Column 1] is quantity * cost_total.

The application is very very slow loading the pivot...

Anyone can help me to optimize this? 😞

Thanks

Mike

[marcus_sommer · Accepted solution]

I think this could be optimized by replacing the inner if-condition with a outer if-condition - see: Re: Sum(if(...)) vs If(Sum(...), Sum(...)) - and probably some more by replacing it through a set analysis. Therefore try something like this:

[Column1 ] /

Sum(TOTAL <bu, line, prj, year, month>

Aggr(if(dt_date_end=dt_month_cost_end, Sum(quantity)) *

         if(dt_month_cost=V_MAX_DT, sum(distinct cost_total)),

supplier, bu, line, prj, year, month))

[Column1 ] /

Sum(TOTAL <bu, line, prj, year, month>

Aggr(Sum({< dt_date_end = {"=dt_date_end=dt_month_cost_end"}>} quantity) *

         sum({< dt_month_cost= {"$(V_MAX_DT)"}>} cost_total),

supplier, bu, line, prj, year, month))

- Marcus

[trdandamudi]

Is it possible to post a small sample file with expected output to test it out ?

May be you can give a try on the below:

[Column1 ] /

Sum(TOTAL <bu, line, prj, year, month> Aggr(Sum({$<dt_date_end={"=dt_month_cost_end"}>}, quantity))   *

sum(distinct {$<dt_month_cost={"=$(V_MAX_DT)"}>},cost_total)),supplier, bu, line, prj, year, month))

[mike_spada]

I cannot attach a file because of data too private 😞

I'll try your suggest and let you know.

Thanks,

Mike

[sunny_talwar]

Check this link which might be able to address your data sensitivity issues while sharing a sample

Preparing examples for Upload - Reduction and Data Scrambling