topic Need help using set analysis with aggr in QlikView

Need help using set analysis with aggr

jesper_bagge — Tue, 30 Mar 2010 14:17:52 GMT

I have encountered a problem when using set analysis within an aggr() function. I have two tables:

Data - containing the fields StoreNo, Month, Sales.
Focus - containing the fields StoreNo, focusmonth

What I want to do is sum up the sales per store and month, but only for the distinct store and month specified in my table named Focus.

Using the good old Sum(Aggr(distinct Sum(If(Month = focusmonth, Sales)),Store,Month)) does solve my problem in my small example, but I want to do the same thing using Set-Analysis which - in my head atleast - shouldn't pose a problem. But it does.

Using the expression Sum(Aggr(distinct Sum({$<Month = {$(=Only(focusmonth))}>} Sales),Store,Month)) gives me a zero value.

I've attached an example of this containing 6 rows. Considering that the data I want to use this expression on weighs in at about 70 million rows I'm not sure that the If-statement will be a very practical one.

Does anyone have any bright ideas? Have I missed something?

/cheers!

Need help using set analysis with aggr

Tue, 30 Mar 2010 14:43:49 GMT

Hi Jesper,

Add the set analysis statement to the first Sum as well as the second

Sum({$<Month = {$(=Only(focusmonth))}>} Aggr(distinct Sum({$<Month = {$(=Only(focusmonth))}>} Sales),Store,Month))

This will work when you have one focus month selected.

/Johan

Need help using set analysis with aggr

jesper_bagge — Tue, 30 Mar 2010 14:52:37 GMT

Hi Johan,

If I select one focus month, I don't even have to add the set-statement to the first sum.

However, this doesn't solve my problem. I will have approximately 20000 variants of these focus months in my live data and selecting one at a time isn't a viable option

Need help using set analysis with aggr

Tue, 30 Mar 2010 15:01:34 GMT

The way you have written the set analysis statement, using the only function, requires you to have one focus month selected. I'm not sure you can get the desired results using set analysis.

Need help using set analysis with aggr

jesper_bagge — Tue, 30 Mar 2010 17:23:21 GMT

Hi Johan,

The If-statement also uses an only-function, and should only work with one focus month selected. But since I use it within an aggr, only one possible month exists for every StoreNo. Thats the beauty of the Aggr function.

So, still at square one. It works with a regular If-statement, but not using the same logic in a set-statement.

Anyone else have any input on this?

Need help using set analysis with aggr

Anonymous — Thu, 21 Jul 2011 11:56:38 GMT

Hey Jesper,

Have u found asolution to this problem?

I have a similar problem using set analysis inside aggr.

Trying to calculate:

Avg(aggr(count({$<DATE= {“>=$(DinamicDate)”}>} distinct Customers),DATE))

Where DinamicDate= DATE-3

Actually If I Select one day, I want to count the number of customers in the last 3 days.

If I Selectmore than one Date I want the avg of that count aggr by date.

Thanks for any help..