topic Re: Aggr taking time in Stacked Bar Chart in QlikView

Aggr taking time in Stacked Bar Chart

amit_saini — Thu, 06 Sep 2018 09:19:23 GMT

Hi Folks,

I'm using below exp in a Stacked bar chart:

=Sum({<Month=,Week=,Why_Name=,Standard_Name=,Area_Name=,Product_Name=,Variant_Name=,Reason_Name=>}Aggr(if(match(Line_Id,'124','116')

,sum({<Month=,Week=,Why_Name=,Standard_Name=,Area_Name=,Product_Name=,Variant_Name=,Reason_Name=>} ScheduledMinutes)+avg({<Month=,Week=,Why_Name=,Standard_Name=,Area_Name=,Product_Name=,Variant_Name=,Reason_Name=>}15),

sum({<Month=,Week=,Why_Name=,Standard_Name=,Area_Name=,Product_Name=,Variant_Name=,Reason_Name=>}ScheduledMinutes))/60,Station_Label,Reason_Planned_Downtime,Week))

Dimm are :

1. Week

2. Reason_Planned_Downtime

Currently , it is taking around 40-50 sec to load the chart , any suggestions to reduce the load time???

In general , what I'm trying is : if(match(Line_Id,'124','116'), ScheduledMinutes+15 ,ScheduledMinutes)/60

Thanks,

Re: Aggr taking time in Stacked Bar Chart

YoussefBelloum — Thu, 06 Sep 2018 09:24:16 GMT

Hi,

maybe replace the the if match inside the set analysis, like this:

{<...,Line_Id={'124','116'}>}

Re: Aggr taking time in Stacked Bar Chart

amit_saini — Thu, 06 Sep 2018 09:36:50 GMT

Hi,

Based on this "if" itself , we have two conditions , if I bring this within set the my expression is wrong.

Can u share full exp , what u meant?

Thanks,

Re: Aggr taking time in Stacked Bar Chart

YoussefBelloum — Thu, 06 Sep 2018 09:45:38 GMT

My bad, you're right.. didn't pay enough attention to the entire post

Re: Aggr taking time in Stacked Bar Chart

amit_saini — Thu, 06 Sep 2018 09:47:34 GMT

Re: Aggr taking time in Stacked Bar Chart

YoussefBelloum — Thu, 06 Sep 2018 09:50:37 GMT

maybe Valuelist() function can help you

Re: Aggr taking time in Stacked Bar Chart

jonathandienst — Thu, 06 Sep 2018 10:30:56 GMT

Aggr() is expensive to calculate. Do you really need it here? Did you try

if(match(Line_Id,'124','116'),

sum({<Month=,Week=,Why_Name=,Standard_Name=,Area_Name=,Product_Name=,Variant_Name=,Reason_Name=>} ScheduledMinutes) + 15,

sum({<Month=,Week=,Why_Name=,Standard_Name=,Area_Name=,Product_Name=,Variant_Name=,Reason_Name=>} ScheduledMinutes) / 60

)

Re: Aggr taking time in Stacked Bar Chart

andrey_krylov — Thu, 06 Sep 2018 10:52:42 GMT

Hi, Amit. Very interesting... try to test the expression in parts. Once I came across the fact that Pick() function in dimension and expression took several minutes and after replacing with simple IF(), everything was fine.

Re: Aggr taking time in Stacked Bar Chart

amit_saini — Thu, 06 Sep 2018 10:56:24 GMT

Hi Jonathan,

Yes, in starting I was using the same expression , but then I found if I'm using the same expression with Different Dimension like Station_Label or Reason_Planned_Downtime or Week different output , but when I used Aggr() results across all Charts was same.

Thanks,

Re: Aggr taking time in Stacked Bar Chart

amit_saini — Thu, 06 Sep 2018 10:57:52 GMT

Hi Andrey,

Yes, my Aggr part is the issue , if I remove it than calculation is super fast.

Thanks,

Re: Aggr taking time in Stacked Bar Chart

jonathandienst — Fri, 07 Sep 2018 15:06:22 GMT

And if you override the selections in those fields?

There are a few ways to reduce the time taken to do the aggr()

Reduce the number and cardinality (distinct values) of the dimension fields
Preselect a subset before the Aggr(), like Sum({<...some set selection...>} Aggr(......))
Compel the users to make selections and use a conditional calc to only calculate after they have made some selections
Make sure you use the simplest possible expression inside the aggr(). This will help some, but a 100.000 x 1.000 x 100 x 100 hyper-cube will still take really long to calculate (and use a lot of RAM).

Re: Aggr taking time in Stacked Bar Chart

jyothish8807 — Wed, 19 Sep 2018 04:41:10 GMT

Hi Amit,

Try like this once:

if(match(Line_Id,'124','116')

,Aggr(sum({<Month=,Week=,Why_Name=,Standard_Name=,Area_Name=,Product_Name=,Variant_Name=,Reason_Name=>}ScheduledMinutes)

avg({<Month=,Week=,Why_Name=,Standard_Name=,Area_Name=,Product_Name=,Variant_Name=,Reason_Name=>}15),Reason_Planned_Downtime,Week),

Aggr(sum({<Month=,Week=,Why_Name=,Standard_Name=,Area_Name=,Product_Name=,Variant_Name=,Reason_Name=>}ScheduledMinutes))/60,Reason_Planned_Downtime,Week)

)

*I have removed "Station_Label" from the aggr, add it if it is required.Also check the brackets once

Br,

Re: Aggr taking time in Stacked Bar Chart

amit_saini — Wed, 19 Sep 2018 07:39:30 GMT

Sorry KC,

Looks like you are trying to suggest 2 aggr function for each condition , this is even more time consuming.

Thanks,

Re: Aggr taking time in Stacked Bar Chart

jyothish8807 — Wed, 19 Sep 2018 07:54:56 GMT

Else you can try creating the aggregation in the script and then use the field directly in the expression. That will be best way to optimizes the performance.

Something like this:

Load

sum(ScheduledMinutes) as New_ScheduledMinutes,

Reason_Planned_Downtime,

Week

resident Table

group by

Reason_Planned_Downtime,

Week;

Then use "New_ScheduledMinutes" in your expression.

Br,