topic Re: SET ANALYSIS RANGE SUM in QlikView

SET ANALYSIS RANGE SUM

Wed, 11 Sep 2013 15:53:28 GMT

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.

Re: SET ANALYSIS RANGE SUM

Gysbert_Wassenaar — Wed, 11 Sep 2013 16:39:30 GMT

Try:

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

Re: SET ANALYSIS RANGE SUM

Wed, 11 Sep 2013 20:55:18 GMT

It don't works, all results are 0.

Re: SET ANALYSIS RANGE SUM

Clever_Anjos — Wed, 11 Sep 2013 21:05:03 GMT

Try slightly different

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

Re: SET ANALYSIS RANGE SUM

Wed, 11 Sep 2013 21:23:41 GMT

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

Anonymous — Thu, 12 Sep 2013 00:10:38 GMT

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

Thu, 12 Sep 2013 22:00:05 GMT

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