Aggregated function within Set analysis

[#1]

Hello,

my fact is as followed:

empID, LineID,YearMonthPaid,YearMonthValue,Amount

I wish to show case all employees who were paid less a specified amount. the user will input the amount into the variable vMinAmount

usually i would create an aggr() expression in the dimension and use the suppress null values. however this a very expensive operation and for a large data set takes too long or doesn't work at all.

if(aggr(sum(Amount),empID,YearMonthPaid)<$(vMinAmount),empID)

So, I have seen in one of the posts that there is a possibility to put the condition inside set analysis and use up less system resources, I have written the following expression:

Sum({$<Amount={"=Sum(Amount)<$(vMinAmount)"}>}Amount) and the dimensions in the chart are: empID, YearMonthPaid

an employee can have more than one payment on the same YearMonthPaid, I am interested in the sum of all lines and not each one apart.

Now, this expression showד results, however it is the same as writing: Sum({$<Amount={"<$(vMinAmount)"}>}Amount)

There is no aggregation happening, so if an employee have 2 lines and I wish to view all the employees with less than a 1000, for employee 111 (following example) i am not supposed to get any results (since he received 1560 in total), however I get the first line and amount is -440.

EmpID	YearMonthPaid	YearMonthValue	Amount
111	2014-06	2014-06	-440
111	2014-06	2014-05	2000

Is there a way to accomplish this?

thank you

Nir

[chriscammers · Accepted solution]

First of all, give the help page for Set Analysis a thorough read, it is a good overview of the feature.

To achieve your requirement you need to make your set based on the employee, not the amount, Like this

sum( {$<EmpID = {“=Sum(Amount) < $(vMinAmount)”}>} Amount )

I hope this helps.

[#3]

Hey Chris,

thanks! works great.

I have seen just one example of using aggregate function within a set analysis expression, I personally believe that set analysis is preferable to using an if statement, where ever possible. every time I use set analysis instead of IF, it speeds up calculation, especially noticeable with  large data sets. however, usually most posts refer to a IF+aggr() combination which I am happy to replace, if I can, especially with an external parameter.

which help page did you refer to?