Difference between the AVG of Current Month and the AVG of the LAST DAY of the PREVIOUS Month

[LucyN]

Hi,

I would like to compare the Avg Rate of the current month with the Avg Rate on the LAST DAY of the PREVIOUS Month.

My data looks like this

The data table goes all the way to 11/01/2019. From this data set, I want to make a table where I can compare the Avg Rate of the current month with the Avg Rate on the LAST DAY of the previous month.

1. Since the selected time range starts from 2017-Nov, so instead of calculate the difference, I want to display the Rate on the last day of November

2. From 2017-12 and forward, I want to calculate compare the Avg Rate of the current month with the Avg Rate on the LAST DAY of the previous month (please see screenshot)

[edwin]

i would create a bridge between your fact and the calendar.

the bridge will have a one to one link between calendar date and fact date - these are your CURRENT date type
it will also have a link between each date in the calendar to the END OF MONTH date in the fact - these are your PRIOR month date type

therefore if 12/1/2020 month is selected.  all dates in 12/2020 will be selected in the fact for date type = CURRENT,  also 11/30/2020 is selected in the FACT for date type = PRIOR

see sample attached

[LucyN]

Edwin,

Thanks for getting back to me. Unfortunately, I'm using Qlik Sense cloud, so I couldn't open your attachment. Can you please kindly send me the screenshots? Much much appreciated!!

[edwin]

hope this helps:

data:

load 
1 as Region,
date(date('01/01/2020')+ RowNo()) as dt,
floor(rand()*100000)/100 as amnt
AutoGenerate (365)
;

cal:
load distinct dt as Date, MonthStart(dt) as Month
Resident
data;

NoConcatenate
bridge:
load
Date as dt,
Date as Date,
'CURRENT' as DateType
Resident
cal;

noConcatenate 
PriorMonth:
load
Date as Date,
'PRIOR' as DateType
Resident
cal;

inner join (PriorMonth)
load
Date as dt
Resident
PriorMonth
;

Concatenate (bridge)
load
*
Resident
	PriorMonth
where 
monthstart(Date)-1 = dt
;

drop table PriorMonth;

the chart:

expression1: =sum({<DateType={'CURRENT'}>}amnt)
expression2: =sum({<DateType={'PRIOR'}>}amnt)