topic Re: Using AGGR to calculate Like for like (LFL) in QlikView

Using AGGR to calculate Like for like (LFL)

p_verkooijen — Mon, 17 Jun 2013 13:02:14 GMT

I'm using AGGR conditions to determine if Sales Y vs LY is valid.

MAX diff in sales Y vs LY is 0,75 per Location/Week.

I only want to use the valid weeks for the locations.

I've managed to make AGGR for a Top level, but when a make a chart with a Category dimension the numbers are not what they should be.

This should be:

See attachment for example QVW.

Re: Using AGGR to calculate Like for like (LFL)

Gysbert_Wassenaar — Mon, 17 Jun 2013 17:03:00 GMT

See attached qvw. I'm not sure your calculations are correct. But that's for you to figure out. If you select a BenchmarkCategory the numbers change quite a bit. Compare the tables to the left and right of the listbox if when you select BenchmarkCategory values.

Re: Using AGGR to calculate Like for like (LFL)

p_verkooijen — Tue, 18 Jun 2013 15:14:38 GMT

Hi Gysbert,

Tried to implement the solution in the client dashboard, however as you mentioned a selection changes the numbers. Expecially with average calculations.

I've come up with a solution for this problem.

Add a field Location_Week in the facttable
Created a variable which used the likeforlike formula to represent all valid location_week combinations in a Set Analysis string format
Then use this variable in the SA of the formula like SUM({<Year={2013}, $(vValidLocationWeek)>} Sales)

See attached Solution.qvw

Thanks for your help on this.