topic Re: Problem with set analysis in QlikView

Problem with set analysis

geogou1973 — Wed, 25 May 2016 16:50:40 GMT

Hello.

I'm facing a problem with a command in set analysis. I have the following command

=sum({<DATE_ID={'>=$(=previousudate)<=$(=vudate)'},DEL_ITEM={'DEL_ITEM'}>} AMOUNT)

How can I complete the formula to have only values that the sum of the amount is less than 5.000

Thank you in advance

Re: Problem with set analysis

swuehl — Wed, 25 May 2016 17:19:05 GMT

Not sure if I understood correctly, maybe

=RangeMin(

sum({<DATE_ID={'>=$(=previousudate)<=$(=vudate)'},DEL_ITEM={'DEL_ITEM'}>}AMOUNT),

5000

)

Re: Problem with set analysis

geogou1973 — Wed, 25 May 2016 17:31:42 GMT

Hi swuehl.

The formula is working correctly for the items that the amount for example is less than 5000 but at the same time I have a lot of items with amount 5000 that is not correct

Re: Problem with set analysis

jolivares — Wed, 25 May 2016 17:32:38 GMT

Let if I understood your request:

Your amount must be <= 5000, if yes

=sum({<DATE_ID={'>=$(=previousudate)<=$(=vudate)'},DEL_ITEM={'DEL_ITEM'}, AMOUNT = {'<=5000'}>} AMOUNT)

Or sum(...AMOUNT) <=5000

Maybe you need to aggr your data.

Sum({...} If(Aggr()<=5000, AMOUNT))

Otherwise, try to post some data to explain better.

Re: Problem with set analysis

geogou1973 — Wed, 25 May 2016 17:39:22 GMT

like the following formula ?

Sum({<DATE_ID={'>=$(=previousudate)<=$(=vudate)'},DEL_ITEM={'DEL_ITEM'}>} If(Aggr(sum(AMOUNT)<=5000, AMOUNT))

Re: Problem with set analysis

swuehl — Wed, 25 May 2016 17:45:02 GMT

Or maybe

If(

sum({<DATE_ID={'>=$(=previousudate)<=$(=vudate)'},DEL_ITEM={'DEL_ITEM'}>}AMOUNT)

<5000,

sum({<DATE_ID={'>=$(=previousudate)<=$(=vudate)'},DEL_ITEM={'DEL_ITEM'}>}AMOUNT)

)

Re: Problem with set analysis

tamilarasu — Wed, 25 May 2016 18:24:55 GMT

May be try this,

=sum({<DATE_ID={'>=$(=previousudate)<=$(=vudate)'},DEL_ITEM={'DEL_ITEM'}, ITEM_NAME={"=Sum(AMOUNT)<=5000"}>} AMOUNT)

I am not sure about your dimension names. So replace ITEM_NAME according to your chart.

Re: Problem with set analysis

sunny_talwar — Wed, 25 May 2016 18:32:56 GMT

I would suggest to use < 5000 instead of <= 5000 because it seems the OP wants less the 5000

=Sum({<DATE_ID={'>=$(=previousudate)<=$(=vudate)'}, DEL_ITEM={'DEL_ITEM'}, ITEM_NAME={"=Sum(AMOUNT) < 5000"}>} AMOUNT)

Re: Problem with set analysis

swuehl — Wed, 25 May 2016 18:39:11 GMT

But I think the same filters need to be applied also in the advanced search, then?

=Sum({<DATE_ID={'>=$(=previousudate)<=$(=vudate)'}, DEL_ITEM={'DEL_ITEM'}, ITEM_NAME={"=Sum({<DATE_ID={'>=$(=previousudate)<=$(=vudate)'}, DEL_ITEM={'DEL_ITEM'} >} AMOUNT) < 5000"}>} AMOUNT)

Not sure if this is better performing then my simple if() condition.

Re: Problem with set analysis

geogou1973 — Wed, 25 May 2016 18:42:08 GMT

Hi Sunny.

It seems that is working.

Thank you very much

Re: Problem with set analysis

tamilarasu — Wed, 25 May 2016 18:46:36 GMT

I am bit confused about the OP's requirement whether it is <5000 or <=5000. Lets wait for the reply.

gou geo wrote:

The formula is working correctly for the items that the amount for example is less than 5000 but at the same time I have a lot of items with amount 5000 that is not correct

Re: Problem with set analysis

tamilarasu — Wed, 25 May 2016 18:57:17 GMT

Sunny,

While replying to you, the thread has been closed. This is what happened many times when I use my phone to reply. Very hard to reply. Any way, he got the solution.