topic Month/Year vs Month/Prior Year in QlikView

Month/Year vs Month/Prior Year

Anonymous — Sat, 27 May 2017 23:17:41 GMT

Hi all,

Still working my way through Qlik and could definitely use some help from all the gurus here.

I am trying to create a year over year comparison when a user selects a single month from the chart. I would like the selected month to

display alongside that same month 1 year prior (Example: if Jan/2017 is selected, I would like to see Jan/2016 and Jan/2017 display instead of just Jan/2017) . If I can do this in one chart, it would be ideal, but if i need to make a separate chart, that is not a problem.

My current chart is displaying a count of users from a baseline period (user defined) with the latest 6 months of data.

I have created this using an if statement in the load script assigning 'Baseline' as a value if the user falls between a certain date range, otherwise I use Month/Year in a created field.

I used Henric Cronström's MonthsAgo trick to sort these values as well as to only display the last 6 months of data. I originally thought I could use it again to add 12 to whatever the value is for MonthsAgo when a date is selected, but my knowledge of Qlik is not so advanced currently to write something like that.

Code in load script:

If(Date >= '01/01/2015' and Date <= '12/31/2015', 'Baseline', (CMonth&'/'&CYear)) as BaselineClass,

12*(Year(Today())-Year(Date)) + Month(Today()) - Month(Date) as MonthsAgo,

Code for Chart (BaselineClass is my dimension)

=if(BaselineClass = 'Baseline',Count({$<CYear = {2015}>}User)/12,Count({$<MonthsAgo={">=1<7"}>} User))

Thanks for any help you can provide.

Re: Month/Year vs Month/Prior Year

Anil_Babu_Samineni — Sun, 28 May 2017 03:51:27 GMT

Good to hear, Atleast you got some information from some one (HIC)

Here, What was the challenging you are facing?

Re: Month/Year vs Month/Prior Year

Anonymous — Sun, 28 May 2017 05:13:17 GMT

So currently when I select a single month, i see a single month

What I am looking to make (either in the same chart or in a separate chart):

When a month is selected, the previous year for that month is also displayed. Something like this:

My issue is I don't know the best way to attack the problem, my chart dimension is a field created in the load script (BaselineClass), which is concatenating the month and the year. So I don't know how to manipulate the year, while holding the month constant to display what i need. I also had hope that I could just utilize the MonthsAgo field to display the month selected and the month selected +12 months ago, but as i said above, I'm not so well versed in qlik expressions to even know where to begin with that.

Any help is appreciated!

Re: Month/Year vs Month/Prior Year

Anil_Babu_Samineni — Sun, 28 May 2017 05:18:01 GMT

You have to make like below for Next Year Sales of selection. If possible please share sample application so that we can work

Sum({<MonthYear = {'$(=AddMonths(Max(MonthYear), 1))'}>} Sales)

Re: Month/Year vs Month/Prior Year

Anonymous — Sun, 28 May 2017 05:57:35 GMT

Not sure what to do with this. I made a new chart using a calculated dimension: CMonth & '/' & CYear

The Expression for previous year results in No Data

Count({<CYear= {'$(=AddMonths(Max(CYear), -1))'}>} User)

Re: Month/Year vs Month/Prior Year

Anil_Babu_Samineni — Sun, 28 May 2017 06:05:21 GMT

You must create Field instead of Calc. Dimension. Or else please provide sample application to look where you are doing this?

Re: Month/Year vs Month/Prior Year

Anonymous — Sun, 28 May 2017 06:31:28 GMT

Here is the sample you requested

Re: Month/Year vs Month/Prior Year

avinashelite — Sun, 28 May 2017 06:47:42 GMT

Try like this ...

Count({<CYear= {"$(=(Max(CYear) -1))"},CMonth ={"$(=(max(CMonth)))"}>} User)

Month/Year vs Month/Prior Year

Re: Month/Year vs Month/Prior Year

antoniotiman — Sun, 28 May 2017 08:20:52 GMT

Hi Neil,

see attachment

Regards,

Antonio

Re: Month/Year vs Month/Prior Year

Anonymous — Mon, 29 May 2017 22:33:39 GMT

Thanks Antonio! It's exactly what I was hoping to do. I appreciate it very much.