6 Replies Latest reply: Sep 12, 2013 6:00 PM by Manuel Capella Monsonís

# SET ANALYSIS RANGE SUM

Good afternoon.

I want to calculate an amount (sum), but only for customers in a certain range (between two variables, that can be modified by the user). The user selects defines the range with vMinRev and vMaxRev. Only those customers in range should appear. The amount (sum) for a customer must be between vMinRev and vMaxRev.

I tried with aggr, and with set analysis:

This is my set analysis function:

Sum({\$<Concept0={'REV'},Cod_Cust=P({\$<Sum({<Concept0={'REV'} Amount={">=\$(=vMinRev)<=\$(=vMaxRev)"}>}Cod_Cust)>}Amount).

Thanks.

• ###### Re: SET ANALYSIS RANGE SUM

Try:

Sum({\$<Concept0={'REV'},Cod_Cust={"=Sum({<Concept0={'REV'}>} Amount)>=\$(vMinRev) and Sum({<Concept0={'REV'}>} Amount) <=\$(vMaxRev)"}>}Amount)

• ###### Re: SET ANALYSIS RANGE SUM

Try slightly different

Sum({\$<Concept0={'REV'},Cod_Cust=P({\$<Sum({<Concept0={'REV'},Amount={">=\$(=vMinRev)"}*{<=\$(=vMaxRev)"}>}Cod_Cust)>}Amount).

• ###### Re: SET ANALYSIS RANGE SUM

Don't works, all results are blanks. I think that Sum({\$<Concept0={'REV'},Cod_Cust={"=Sum({<Concept0={'REV'}>} Amount)>=\$(vMinRev) and Sum({<Concept0={'REV'}>} Amount) <=\$(vMaxRev)"}>}Amount) will work ok, but it don't returns the Cod_Cust on range set.

• ###### Re: SET ANALYSIS RANGE SUM

Hi, it was not sure for me if you wanted to sum to be between the Variables, or to sum where the single amount is between the variables.

Here are the 2 solutions, and na example file.

Single Value Between Var :

Sum(  {<Amount  = {'>=\$(=vMinRev),  <=\$(=vMaxRev)'} > } Amount )

Sum between Vars :

( if (
Aggr( NODISTINCT sum( Amount ) , CodCus ) > \$(vMinRev) and
Aggr( NODISTINCT sum( Amount ) , CodCus ) < \$(vMaxRev)
,
Amount , 0 )
)

Sorry if i have not understood it.

Ps -  still have to add the Concept0 set.

• ###### Re: SET ANALYSIS RANGE SUM

I have found the solution:

Sum({\$<Concept0={'REV'}, COD_Cust={"=Sum({\$<Concept0={'REV'}>} Amount)>= \$(vMinRev) AND Sum({\$<Concept0={'REV'}>} Amount)<= \$(vMaxRev)"}>} Amount)

I think that G Wassenaar solution was correct, but some chars was lossen.

Thanks everybody!!