Optimizing dynamic aggr() function

[#1]

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!

[Gysbert_Wassenaar · Accepted solution]

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

[#3]

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!

[#4]

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

[Gysbert_Wassenaar]

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

[#6]

Hi Gysbert,

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

[Gysbert_Wassenaar]

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.

[#8]

Thats it ! perfect thanks allot.

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