topic Re: Sum total aggr... help to optimize in QlikView

Sum total aggr... help to optimize

mike_spada — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Sum total aggr... help to optimize

marcus_sommer — Sun, 08 May 2016 14:19:26 GMT

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

Re: Sum total aggr... help to optimize

trdandamudi — Sun, 08 May 2016 14:20:09 GMT

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))

Re: Sum total aggr... help to optimize

mike_spada — Sun, 08 May 2016 15:05:32 GMT

I cannot attach a file because of data too private 😞

I'll try your suggest and let you know.

Thanks,

Mike

Re: Sum total aggr... help to optimize

sunny_talwar — Tue, 10 May 2016 01:19:23 GMT

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