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: 
geogou1973
Creator
Creator

Problem with set analysis

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

1 Solution

Accepted Solutions
sunny_talwar

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)

View solution in original post

11 Replies
swuehl
MVP
MVP

Not sure if I understood correctly, maybe

=RangeMin(

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

5000

)


?

geogou1973
Creator
Creator
Author

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

jolivares
Specialist
Specialist

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.

geogou1973
Creator
Creator
Author

like the following formula ?

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

swuehl
MVP
MVP

Or maybe

If(

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

<5000,

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

)

tamilarasu
Champion
Champion

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.

sunny_talwar

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)

swuehl
MVP
MVP

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.



geogou1973
Creator
Creator
Author

Hi Sunny.

It seems that is working.

Thank you very much