topic Re: Optimizing dynamic aggr() function in QlikView

Optimizing dynamic aggr() function

Tue, 15 Oct 2013 15:44:51 GMT

Dear QlikView fanatics,

I am struggeling for quite some time now to create a dynamic LFL data set based on store opening and closing.

With some help i came up with this Aggr() statement for Turnover (index) LFL rolling 13 weeks vs previous LFL rolling 13 weeks.

     sum(aggr(if((OpenYearWeek) <= MaxString({$<PeriodID = {"<=$(=Max(PeriodID)-25)"},Week=,Year=>}YearWeek)
     AND (ClosedYearWeek) >= MaxString(YearWeek),sum({$<PeriodID = {">=$(=Max(PeriodID)-12)<=$(=Max(PeriodID))"},
     Year = ,
     Week=,
     YearWeek=>} #NetValue_EUR),0), StoreName, Country))
     /
     sum(aggr(if((OpenYearWeek) <= MaxString({$<PeriodID = {"<=$(=Max(PeriodID)-25)"},Week=,Year=>}YearWeek)
     AND (ClosedYearWeek) >= MaxString(YearWeek),sum({$<PeriodID = {">=$(=Max(PeriodID)-25)<=$(=Max(PeriodID)-13)"},
     Year = ,
     Week=,
     YearWeek=>} #NetValue_EUR),0), StoreName, Country))

I use the same aggr function for conversionrate/grossmargin/visitors and so on. This calculation works properly although the performance is real shit.:(

I could also flag stores in script for LFL store last13weeks. But i would like to keep the function that users can select a different week than the current.

Could anyone point me in the right direction here? Any help is much appriciated!

Re: Optimizing dynamic aggr() function

Gysbert_Wassenaar — Tue, 15 Oct 2013 16:38:18 GMT

You can probably optimize things by creating an AsOf table to easily calculate the rolling 13 weeks amounts with good performance. Also create a numeric YearWeek field so you can use max instead of maxstring. Perhaps you can also replace the if statements with set modifiers. Can you post an example document? Read this document for how to create a non-confidential example: Preparing examples for Upload - Reduction and Data Scrambling

Re: Optimizing dynamic aggr() function

Wed, 16 Oct 2013 07:53:21 GMT

Hi Gysbert,

First, Thanks allot!

I think you helped me allot allready by pointing me in the right direction. I will try your advice and i'm very glad you are willing to helping me even further by analyzing my qvw.

I don't want to ask more instead of trying more myself. So i will first try to optimize the qvw/calculations myself. I will let you know how this works out!

Thanks again!

Re: Optimizing dynamic aggr() function

Thu, 17 Oct 2013 08:23:50 GMT

Hi Gysbert,

I am almost there, allthough i have 1 small and weird problem. For some reason i can't convert the store atribute OpenYearWeekCode to a number by using: Num(OpenYearWeekCode)

This will result in - value's.

This field contains value's like: 2013W23. I've replaced the W with "/" to create the same format as YearWeek in my Master Calander.

I use the num(Peek) function on my calander to create the current yearweek or -52 etc. To create my initial calculation i will have to have the OpenYearWeekCode converted to the same number format.

Any idea's ? this should be quite simple right?

Thanks in advance

Re: Optimizing dynamic aggr() function

Gysbert_Wassenaar — Thu, 17 Oct 2013 09:22:07 GMT

num#(replace(OpenYearWeekCode,'W','')) should create a numeric value like 201323.

Re: Optimizing dynamic aggr() function

Thu, 17 Oct 2013 10:57:48 GMT

Hi Gysbert,

Yes i can create that format. But the value retrieved by the num(Peek)) function is like 41547 translated from 2013/40?

Re: Optimizing dynamic aggr() function

Gysbert_Wassenaar — Thu, 17 Oct 2013 11:29:37 GMT

That looks like it's the numeric value of a date. =num(MakeWeekDate(2013,40)) returns 41547 in a text box. Perhaps you want to use this: dual(replace(OpenYearWeekCode,'W','/'), makeweekdate(left(OpenYearWeekCode,4),right(OpenYearWeekCode,2))) as OpenYearWeekCode.

Re: Optimizing dynamic aggr() function

Thu, 17 Oct 2013 11:47:41 GMT

Thats it ! perfect thanks allot.

Bedankt man;) mocht ik jouw nog ergens mee kunnen helpen hoor ik het graag!