Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I need to make the difference between last month and previous month and I have the following expression:
Sum({<HIST_DATE={'$(=Max(HIST_DATE))'}>}Aggr(AMOUNT_APPROVED,CONTRACT_NO,CUSTOMER_NO,HIST_DATE))-Sum({<HIST_DATE={'$(=MonthEnd(Max(HIST_DATE),-1))'}>}Aggr(AMOUNT_APPROVED,CONTRACT_NO,CUSTOMER_NO,HIST_DATE))
When I don't have any date selected in a filter the equation work , but if I select a certain date the formula only return the value of the selected date (first expression) but not the previous month (second expression).
The HIST_DATE is in format 'M/DD/YYYY'
Can someone help me?
Kind regards,
ive shown it can be done but what i used was made up data on my end. i think your data has some nuance you have not artuclated so maybe its time you supply your own data and what the expectation is.
there are times you can get away with not using AGGR. but there rae times it is indeed required. whats needed is more understanding of your problem.
it does not have to be real data as long as it is modeled the same way you can use test or fake data
I found the solution. I change the previous month formula with this and it returns the values of previous month when I select a certain date:
Sum({<HIST_DATE=>}Aggr({<HIST_DATE={'$(=monthend(addmonths(vMaxDate,-1)))'}>}max(AMOUNT_APPROVED),HIST_DATE,CUSTOMER_NO,CONTRACT_NO))