Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

SET ANALYSIS RANGE SUM

Good afternoon.

I need your help.

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.

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

6 Replies
Gysbert_Wassenaar

Try:

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


talk is cheap, supply exceeds demand
Not applicable
Author

It don't works, all results are 0.

Clever_Anjos
Employee
Employee

Try slightly different

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

Not applicable
Author

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.

Anonymous
Not applicable
Author

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.

Not applicable
Author

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