Problem with set analysis

[geogou1973]

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

[swuehl]

Not sure if I understood correctly, maybe

=RangeMin(

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

5000

)

?

[geogou1973]

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]

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]

like the following formula ?

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

[swuehl]

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]

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 · Accepted solution]

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]

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]

Hi Sunny.

It seems that is working.

Thank you very much