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: 
p_verkooijen
Partner
Partner

Using AGGR to calculate Like for like (LFL)

Untitled.png

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.

Untitled2.png

This should be:

Knipsel.JPG

See attachment for example QVW.

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand

View solution in original post

2 Replies
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
p_verkooijen
Partner
Partner
Author

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.