Difference between actual month vs previous month

[ValKorel]

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,

[edwin]

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))'}>}

[ValKorel]

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'

[Mark_Little]

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

[edwin]

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:

[ValKorel]

---

---

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.

[edwin]

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

[ValKorel]

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)

[edwin]

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

[ValKorel]

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?