Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jlampard40
Contributor III
Contributor III

Current month activity vs previous month in previous year as %

Hi guys - I am trying to show a chart which demonstrates our COVID recovery, based on the same month but in a non-COVID year - we need to track our % recovery.  So, as an example, if Jan 21 was 1000 patients seen, but Jan 20 was 900, then our recovery is 900/1000=90%.  The problem comes when trying to look at our recovery from March 21 onwards, as the year we will be comparing against is March 20 which is exactly when COVID hit and decimated our patient activity.  So, any sort of recovery will obviously be much better when basing it against March 20 - so ideally for March 21 onwards I'd like to look at the Average of Dec 19, Jan 19, and Feb 19.  Then base March 21 against this average, and so on.

Question is, I've so far been using the following expression:

=count({<DateType={'CURRENT'}, Activity_or_Demand={'Activity'}>}Event_Key)/count({<DateType={'PRIOR'}, Activity_or_Demand={'Activity'}>}Event_Key)

This simply uses current date against prior...but from March 21 onwards I need to base it on the average of Dec 19, Jan 19, and Feb 19.  This all needs to ideally be shown on the same chart so a mix of expressions within the same chart??  I'm not sure how to do this.  Any help much appreciated.

Kind Rgds

1 Reply
Marcos_Ferreira_dos_Santos

Hi Jlampart40,

Have you solved this issue? I hope so.

By seening your exposure, I would think in resolving the "hardest" part (the average of three months...) in script time and thus have a "prepared column" to deal with in the panel.

Kind Rgds

Marcos