Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Optimizing dynamic aggr() function

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!

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand

View solution in original post

7 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Not applicable
Author

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!

Not applicable
Author

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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.


talk is cheap, supply exceeds demand
Not applicable
Author

Thats it ! perfect thanks allot.

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