Set analysis: Count ranges for sums

[#1]

Hello everyone,

I am new in qlikview and I have an issue in a count a little bit complicated.

I have tables with Sales by Product, Banner, Store and Month.

I would like to count the number of store where my goad achievement (SUM(SalesCurrentYear-SalesLastYear) /SUM(SalesLastYear)) are in ranges. So if I select 4 month and for my 200 stores for a few products:

It can count that I have 80 stores under 105% , 60 between 105% and 110% and 60 above 110%. It also want to know that for my 200 store my average is about 105%.

Is it possible with set analysis ? Or should I have to rethink my design ?

Thanks for your help.

[#2]

lookup IntervalMatch....that may help you get where you wish to.

[#3]

I am not sure how I can use this function because It think it is meant to be used in my loading script.

However, when I use my goal achivement it is dynamic:it changes is regarding the stores or the months or the product,... That I have selected. Moreover It cannot be calculated in my script because it is a division of sum.

Any other ideas ?

Thanks for your help.

[swuehl · Accepted solution]

try maybe something like

count( aggr( if( SUM(SalesCurrentYear-SalesLastYear) /SUM(SalesLastYear) < 0.05, Store), Store))

resp.

count( aggr( if( SUM(SalesCurrentYear-SalesLastYear) /SUM(SalesLastYear) >= 0.05

and SUM(SalesCurrentYear-SalesLastYear) /SUM(SalesLastYear) <= 0.1, Store), Store))

resp.

count( aggr( if( SUM(SalesCurrentYear-SalesLastYear) /SUM(SalesLastYear) > 0.1, Store), Store))

for the counts and

avg( aggr( SUM(SalesCurrentYear-SalesLastYear) /SUM(SalesLastYear), Store))

for the average.

Since I don't know your data model, you might need to modify these expressions a little (or you maybe you could tell us a little more about your setting, best with uploading a sample).

Also note that I used e.g. 10% instead of 110%, since your expression is calculating the surplus only (already subtracting 1 for the last years sales. Or use expressions like sum(SalesCurrentYear) / sum(SalesLastYear) .

Or maybe I misunderstood that requirement, then just change the numbers to whatever you like.

Regards,

Stefan

[#5]

Thanks for your help swuehl.

With a few modifications I achieve to do it with with this formula:

count( aggr( if( SUM(SalesCurrentYear-SalesLastYear) /SUM(SalesLastYear) >= 0.05

and SUM(SalesCurrentYear-SalesLastYear) /SUM(SalesLastYear) <= 0.1, Store), Store))

[swuehl]

Good to hear!

BTW, is above the answer you wanted to mark as correct ?

[IAMDV]

nagrapart - Please can you mark the right answer as "Correct Answer"? It helps other users while accessing this post.

Thanks in advance.

Cheers - DV