Aggregate set modifiers in Set Analysis ?

[#1]

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 !

DH

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

[jagan · Accepted solution]

Hi,

Try this expression

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

Hope this helps you.

Regards,

Jagan.

[#3]

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

DH

[swuehl]

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

[#5]

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 !

DH

[#6]

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 !!

DH