Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
jlampard40
Contributor III
Contributor III

Previous year % comparison

Hi guys - I need to produce a graph which shows month on month % compliance with the previous year?  So, as example, Nov-20 against Nov-19 expressed as a %.  Due to COVID we need to see how we are recovering on a monthly basis.

The field we use to count is called AD_EventKey and the date field for Calendar month is called AD_CalendarMonth.  Can you help guys?

Many thanks - rgds.

Labels (3)
1 Solution

Accepted Solutions
edwin
Specialist III
Specialist III

see if the attached will help you

View solution in original post

22 Replies
edwin
Specialist III
Specialist III

there are a few ways to do this one way is to store the current month in a variable
vCurrent=AD_CalendarMonth, vPrior=adyears(vCurrent,-1)

then in your set analysis:

count({<AD_CalendarMonth={'$(vCurrent)'}>}AD_EventKey ) /count({<AD_CalendarMonth={'$(vPrior)'}>}AD_EventKey )

another way is to build a bridge that associates current to prior months and add dataTypes appropriately

count({<DataeType={'CURRENT'}>}AD_EventKey ) /count({<DateType={'PRIOR'}>}AD_EventKey )

i suggest you start with the variables

jlampard40
Contributor III
Contributor III
Author

Hi edwin - it hasn't worked for some reason.  Not sure why.  I have attached the sort of thing I'm looking for.  I wasn't sure what your 'adyears' came from in the variable vPrior=adyears(vCurrent,-1)?  The only year field I have is called Calendar_Year.  Thanks.

edwin
Specialist III
Specialist III

addyears - sorry for the typo

edwin
Specialist III
Specialist III

see if the attached will help you

View solution in original post

jlampard40
Contributor III
Contributor III
Author

Hi edwin - I can see that works perfectly and really what I need with the pre-loaded sums in my spreadsheet I attached in my previous message, but my data consists of hundreds of files all loaded into Qlikview.  So I need some sort of set analysis to be able to calculate this based on my existing loaded data.  I've attached exactly what I'm dealing with (data).  In this data I only want to use the 'AD_Data=Activity' so somehow need to build this into the set analysis...?

The basic count I expression I use, to produce a monthly chart is:

=count(Event_Key)

and I use 'Calendar_Year_Month' as the dimension, in format YYYY-MM.

So I really appreciate your solution and it's exactly what I need to show, but I am just not sure how to do this using my original data.  Let me attach a sample of my actual data to show you what I mean as this is the exact data (albeit anonymised and reduced in columns) but goes to show what I'm working with here.

I'll get there in the end, just need to work hard with you guys for assistance.  Many thanks!

jlampard40
Contributor III
Contributor III
Author

Edwin - there's far more data (going back 5 years) but just wanted to give you extract of what I'm working with here.  Many thanks!  I owe you one if we can sort this.  Rgds

edwin
Specialist III
Specialist III

Volume should not be a concern as long as your server memory can handle it.

going back to your question re AD_Data.  if i understand you corretcly you want to calculate the count of AD EventKey where AD Data = 'Activity',   just add it to all your expressions

AD_Data={'Activity'}

that should limit the specific expression 

jlampard40
Contributor III
Contributor III
Author

Hi Edwin - could you produce a .qvw like you did earlier, based on the sample data I have just sent you?  I'd like to see the variables, dimensions used etc to assist me?  Many thanks!

edwin
Specialist III
Specialist III

i added random dates so there will be more from your xls