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,
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))
i would create a variable
vMaxDate=date(max(HIST_DATE),'M/DD/YYYY')
and in your expression use {<HIST_DATE={'$(vMaxDate)'}>}
addmonths for the previous use {<HIST_DATE={'$(=addmonths(vMaxDate,-1))'}>}
Thank you for the quick answer but it doesn't work. Still the same problem.
When I use addmonths, does not return the last day of previous month for MaxDate on a 30 day month (e.g: vMaxDate='6/30/2022' previous month is '5/30/2022' instead of '5/31/2022'
Hi there,
For date thing like this i always tackle it with flags in script, Makes the set analysis much easier.
IF(MONTH(TempDate)= MONTH(TODAY(1)) AND YEAR(TempDate)=YEAR(TODAY(1)),1) AS CURRENT_MONTH,
IF(MONTH(TempDate)= MONTH(ADDMONTHS(TODAY(1),-1)) AND YEAR(TempDate)=YEAR(ADDMONTHS(TODAY(1),-1)),1) AS PRIOR_MONTH,
Then the set analysis would be
SUM({<CURRENT_MONTH={1}>}AMOUNT_APPROVED) - SUM({<CURRENT_PRIOR={1}>}AMOUNT_APPROVED)
Add in any Aggr you may need
it wasnt clear that you needed to compare end of months. first get the end of month for your max - that is if the current is also end of month:
vMaxDate=monthend(date(max(HIST_DATE),'M/DD/YYYY'))
you can still use the same expressions. if you use addmonths with -1, you get prior end of month
as you can see below, addmonths takes care of 31, 30, 28 end of months:
Sorry if i wasn't clear. I need to compare only EOM.
Still the main problem remains. If I do not select any EOM date the comparison act EOM and prev EOM works, but if I select any EOM date it doesn't work anymore. It returns 0 for the previous EOM.
are you saying there is a field called EOM? and your user selects a single value of EOM?
bec if the user selects the current EOM, then why do you need to get the max of a date field to get the current EOM? also what is the relationship between HIST_DATE and EOM?
pls clarify what fields are selected and what the relationships are between the date fields as that is crucial to your problem.
maybe a picture of your data model would help so there is no guessing of missing info - saves us all time and effort
I used EOM as a abbreviation for end of month dates. in not another field.
I wil explain all the elements:
I set the following variables:
For the max date:
vMaxDate=monthend(date(max(HIST_DATE),'M/DD/YYYY'))
For Current month:
vApprovedMax=Sum({<HIST_DATE={'$(vMaxDate)'}>}Aggr(AMOUNT_APPROVED,CONTRACT_NO,CUSTOMER_NO,HIST_DATE))
For previous month:
vApprovedMaxPrev = Sum({<HIST_DATE={'$(=Monthend(vMaxDate,-1))'}>}Aggr(AMOUNT_APPROVED,CONTRACT_NO,CUSTOMER_NO,HIST_DATE))
The HIST_DATE are:
If I use vApprovedMaxPrev with MonthEnd the result is:
If i use Addmonths:
Sum({<HIST_DATE={'$(=addmonths(vMaxDate,-1))'}>}Aggr(AMOUNT_APPROVED,CONTRACT_NO,CUSTOMER_NO,HIST_DATE))
the result is:
My date has to be 5/31/2022 not 5/30/2022
Now if i do not select any HIST_DATE in the filter the calculations are mare:
when I select any HIST_DATE (e.g: 6/30/2022) the result is:
The previous month is not calculated, also the current vs previous return only the current month value.
The formula for Current vsPrevious is:
$(vSumaAprobataMax)-$(vSumaAprobataMaxPrev)
i think i understand the situation. firstly, as your data is all monedend, you dont need monthend function when computing for maxdate:
vMaxDate=date(max(HIST_DATE),'M/DD/YYYY')
and in your expression use {<HIST_DATE={'$(vMaxDate)'}>}
and i see where you are coming from if the prior month has more days in it than the selected month.
use this {<HIST_DATE={'$(=monthend(addmonths(vMaxDate,-1)))'}>} - this ensures you get the end of prior month
also not sure why you think you need to use AGGR as you are setting a variable AGGR makes this slower. if i understand it you just want the sum of AMOUNT_APPROVE
you can just use
sum({<HIST_DATE={'$(=monthend(addmonths(vMaxDate,-1)))'}>}AMOUNT_APPROVE)
probably posting sample data would have made the exercise a lot easier
Hi,
I've changed the formulas and the problem still persist. Also I've eliminate the AGGR function and now the formula work fine.
I put the AGGR function is because in data I have 2 or more lines with the same AMOUNT_APPROVED on CUSTOMER_NO, CONTRACT_NO and HIST_DATE.
The AMOUNT_APPROVED i need to be summed one time not sum of the all lines.
My data extras is:
HIST_DATE | CUSTOMER_NO | CONTRACT_NO | MATURITY_DATE | AMOUNT_APPROVED |
30/06/2022 | 00000000 | 100CLC0000000000 | 202403 | 494,796 |
30/06/2022 | 00000000 | 100CLC0000000000 | 202206 | 494,796 |
There is another way to do that?