
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Difference between the AVG of Current Month and the AVG of the LAST DAY of the PREVIOUS Month
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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
