topic Re: Previous year % comparison in QlikView

Previous year % comparison

jlampard40 — Fri, 15 Nov 2024 23:29:17 GMT

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.

Re: Previous year % comparison

edwin — Tue, 08 Dec 2020 12:43:56 GMT

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

Re: Previous year % comparison

jlampard40 — Tue, 08 Dec 2020 13:02:07 GMT

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.

Re: Previous year % comparison

edwin — Tue, 08 Dec 2020 14:21:16 GMT

addyears - sorry for the typo

Re: Previous year % comparison

edwin — Tue, 08 Dec 2020 15:15:28 GMT

see if the attached will help you

Re: Previous year % comparison

jlampard40 — Tue, 08 Dec 2020 18:34:04 GMT

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!

Re: Previous year % comparison

jlampard40 — Tue, 08 Dec 2020 18:35:57 GMT

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

Re: Previous year % comparison

edwin — Tue, 08 Dec 2020 19:32:17 GMT

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

Re: Previous year % comparison

jlampard40 — Tue, 08 Dec 2020 19:39:57 GMT

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!

Re: Previous year % comparison

edwin — Tue, 08 Dec 2020 19:45:42 GMT

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

Re: Previous year % comparison

jlampard40 — Tue, 08 Dec 2020 20:29:19 GMT

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.

Re: Previous year % comparison

edwin — Tue, 08 Dec 2020 22:05:21 GMT

np hope it works out for you

Re: Previous year % comparison

jlampard40 — Wed, 09 Dec 2020 11:40:59 GMT

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.

Re: Previous year % comparison

edwin — Wed, 09 Dec 2020 12:59:03 GMT

@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.

Re: Previous year % comparison

jlampard40 — Wed, 09 Dec 2020 13:42:00 GMT

@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.

Re: Previous year % comparison

edwin — Wed, 09 Dec 2020 15:20:48 GMT

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

date(AD_CalYrMth&'-01') as Month

see attached

Re: Previous year % comparison

jlampard40 — Wed, 09 Dec 2020 15:30:01 GMT

@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

Re: Previous year % comparison

edwin — Wed, 09 Dec 2020 15:49:34 GMT

@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

Re: Previous year % comparison

jlampard40 — Wed, 09 Dec 2020 16:17:20 GMT

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

Re: Previous year % comparison

jlampard40 — Wed, 09 Dec 2020 16:35:21 GMT

@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