Month Over Month Comparison on Date Level

[ibdK4evr]

Hi,

I need to do a Comparison of Months data on date level. For Example User can select a date range from the date selector like from feb 15 - Mar 15 2020 ,i want to show the values of the previous month of the selected date range (jan 15 - Feb 15 2020 ).

I was able to achieve the same for the year over year comparison, But when i do similar implementation for Month over Month comparison it is not possible.The reason is for example when i get the previous dates for feb month i can get only 29 dates from Jan month and i will be losing two dates 30 jan and 31 jan.

I have attached the calendar implementation for reference.Please feel free to add suggestions.

CALENDER:

//LY is to calculate the previous year date

Load
CALENDAR_DATE,
CALENDAR_YEAR,
CALENDAR_MONTH,
CALENDAR_MONTH_ID,
CALENDAR_YEARMONTH,
Date(dt, 'MM/DD/YYYY') -365 as dt,
'LY' as Flag,
dt as Date
Resident DATE_CALENDAR
where dt>='2018-01-01';

Concatenate(CALENDER)

//LM is to calculate the previous month date

Load
CALENDAR_DATE,
CALENDAR_YEAR,
Month(AddMonths(Date(dt, 'MM/DD/YYYY'),1)) as CALENDAR_MONTH,
CALENDAR_MONTH_ID,
CALENDAR_YEARMONTH,
Date(dt, 'MM/DD/YYYY') as dt,
'LM' as Flag,
dt as Date
Resident DATE_CALENDAR
where dt>='2018-01-01';

Concatenate(CALENDER)

//TY is to calculate the current year and current month date

Load
CALENDAR_DATE,
CALENDAR_YEAR,
CALENDAR_MONTH,
CALENDAR_MONTH_ID,
CALENDAR_YEARMONTH,
Date(dt, 'MM/DD/YYYY') as dt,
'TY' as Flag,
dt as Date
Resident DATE_CALENDAR
where dt>='2018-01-01';

[sunny_talwar]

what do you want to compare Feb 29th 2020 to? Jan 31st or Jan 29th? What were you comparing Feb 29th 2020 to for previous year? Feb 28th 2019? what would you do for Feb 28th 2021? Compare it to Feb 28th 2020 or Feb 29th 2020?

[ibdK4evr]

Hi,

So this is what we are trying to achieve . It is a comparison of two months data based on the month level(MTD). so in this scenario the users selection on date won't affect the values showing in the table. 

In the second table what we have implemented is - we are showing the aggregated value of the current month in 2020(Data is in the date level ).The previous month column has the aggregated value of data from the previous month.In this table you can see the jan value in the 2020 column and the previous month value of feb is not matching.This is because we are calculating the previous months date from the current months date and hence jan is missing 30 and 31 data

 It would be helpful to suggest some way to show the comparison two months value on date level - if you select a date range of feb 1 2020 - feb 29 2020 it should give the  whole feb value in 2020 column and show the aggregated value of jan 1 - jan 31 2020 in the previous month column. 

[sunny_talwar]

What expressions are you using here?

[ibdK4evr]

Hi ,

To Calculate the Current Month value 

Sum({<Flag={'TY'}>}Visits)

To Calculate the Current Month value 

Sum({<Flag={'LM'}>}Visits)

To know the representation of the flags, pls refer the first comment it has the calendar implementation.