Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
Not sure if I understood correctly, maybe
=RangeMin(
sum({<DATE_ID={'>=$(=previousudate)<=$(=vudate)'},DEL_ITEM={'DEL_ITEM'}>}AMOUNT),
5000
)
?
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
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.
like the following formula ?
Sum({<DATE_ID={'>=$(=previousudate)<=$(=vudate)'},DEL_ITEM={'DEL_ITEM'}>} If(Aggr(sum(AMOUNT)<=5000, AMOUNT))
Or maybe
If(
sum({<DATE_ID={'>=$(=previousudate)<=$(=vudate)'},DEL_ITEM={'DEL_ITEM'}>}AMOUNT)
<5000,
sum({<DATE_ID={'>=$(=previousudate)<=$(=vudate)'},DEL_ITEM={'DEL_ITEM'}>}AMOUNT)
)
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.
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)
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.
Hi Sunny.
It seems that is working.
Thank you very much