topic Re: Aggregate set modifiers in Set Analysis ? in QlikView

Aggregate set modifiers in Set Analysis ?

Wed, 13 Feb 2013 15:10:54 GMT

hi All

wondering if anyone can help with this. I want to use a set modifer that takes into consideration the total of multiple lines of data not just line by line.

scenario - I want to exclude from my average, those people that had a total sales of zero not just line level sales of zero

=median({<SalesRep=,aggr(sum({<SalesRep=>}(Revenue,SalesRep)={">0"},TransactionDate={'$(=date(max(TransactionDate)))'}>} TOTAL aggr(sum({<SalesRep=>}(Revenue,SalesRep))

If I remove the part in red and replace it with just Revenue={">0"} then it eliminates line level data which is not what I want(althought that does work perfectly) - I need to only eliminate those sales reps with totals of zero or less.

Here is some data to assisit - I want to eliminate Salesrep A and D and B all of whom have a sum(Revenue) of zero or less. The code that works would only eliminate guy B who at the line levle has a value of zero.

Are set modifiers therefore capable of taking into consideration totals oer multiple lines ?

All thoughts greatly appreciated

thanks !

SalesRep	Revenue
A	-5
A	5
B	0
C	20
C	10
D	-10
D	10

Re: Aggregate set modifiers in Set Analysis ?

jagan — Wed, 13 Feb 2013 15:24:27 GMT

Hi,

Try this expression

=sum({<SalesRep={'=Sum(Revenue)>0'}>} Revenue)

Hope this helps you.

Regards,

Jagan.

Re: Aggregate set modifiers in Set Analysis ?

Wed, 13 Feb 2013 16:47:27 GMT

Jagan

thanks for suggestion and reply. In your example the Sum(Revenue) >0 is not at the salesperson level - i.e. the aggr() function which would be similar to a GROUP by in SQL is not there. so it appears to be evaluating the total SUM(Revenue) not just sum(Revenue) for that salesrep. any suggestions ?

thank you in advance

Re: Aggregate set modifiers in Set Analysis ?

swuehl — Wed, 13 Feb 2013 17:30:23 GMT

DH,

have you tried what Jagan suggested? Using an advanced search as set modifier for field SalesRep is aggregating at SaleRep level, so you should get what you want (only Revenue for SalesRep C).

Regards,

Stefan

Re: Aggregate set modifiers in Set Analysis ?

Wed, 13 Feb 2013 17:53:08 GMT

Stefan/Jagan

thanks yes I just tinkered a little more and was able to make it work but I need to not have a filter affect it. My revised code is as follows.

=median({<SalesRep={'=Sum(Revenue)>0'},TransactionDate={'$(=date(max(TransactionDate)))'}>} TOTAL aggr(sum(Revenue),SalesRep))

This provides the Median I am looking for BUT when I filter on SalesRep it reduces the set to that salesperson. I want the filter on salesrep to not affect the Median

I can generate some data and upload if necessary

many thanks guys !

Re: Aggregate set modifiers in Set Analysis ?

Wed, 13 Feb 2013 18:02:13 GMT

Gents

I just added a {1} and it appears to work perfectly !!

here is my final code

=median({<SalesRep={'=Sum({1} Revenue)>0'},TransactionDate={'$(=date(max(TransactionDate)))'}>} TOTAL aggr(sum(Revenue),SalesRep))

Really grateful guys for your help !!