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

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)
22 Replies
jlampard40
Contributor III
Contributor III
Author

Edwin - that's really great, thanks so much.  I'll sort out the load script etc tomorrow when I'm in the office as the network is lightning quick and I can upload really quick and test it.  Appreciate all your help and will let you know how it works out for me tomorrow!  Many thanks and kind regards.

edwin
Specialist III
Specialist III

np hope it works out for you

jlampard40
Contributor III
Contributor III
Author

edwin - I'm getting exactly the same values for 'Current' and 'Prior'...?  Checked everything but am not sure how you have created the dimension you are using and wonder if that's where I'm going wrong?  I've used dimension of Calendar_Year_Month but you have used '=date(Date,'MM YYYY')'.  Do I need to use exactly the one you have done so and, if so, how do I create this dimension?  Many thanks.

edwin
Specialist III
Specialist III

@jlampard40 the sample i attached was intended to showcase the solution and you should adjust it to your specific case, use your fields and not use mine.  what i did do was teak your data to become YYYY-MM-DD as it is a lot easier to load the date as start of the month.  make sure your field CalendarYearMonth is an actual date field.  this logic will work only with date fields.

sharing a test version of your QVW would be the easiest.

jlampard40
Contributor III
Contributor III
Author

@edwin  - I've attached sample QVW and CSV I'm basing this on.  Obviously my actual file is much larger and the qvw is larger, but these are the two main fields I'm using for this test and all I need to try and achieve what I need to i.e. current vs previous.  What am I doing wrong?  Thanks for your time edwin, really appreciate it.

I've changed some data on the load script but you may just want to save it your end and re-load it to see what's going on.

edwin
Specialist III
Specialist III

the problem is that you dont have a good date field.  i converted your date column :

date(AD_CalYrMth&'-01') as Month

see attached

jlampard40
Contributor III
Contributor III
Author

@edwin - thanks for that!  One more thing before I test my data.  You produced some chart tables with the percentage relating to current / prior, but wondered how to do this on a chart?  Kind regards

edwin
Specialist III
Specialist III

@jlampard40 

You produced some chart tables with the percentage relating to current / prior, but wondered how to do this on a chart?

not sure what you mean that is already a chart.  if you want it converted to a different chart, just change the chart type

jlampard40
Contributor III
Contributor III
Author

@edwin - I owe you a couple of massive favours!!!!!  Thanks so much.  All the best.  

jlampard40
Contributor III
Contributor III
Author

@edwin - quick one, how can I format each column of the bar chart as colour GREEN if the % is equal to or over 100%?  Rgds