Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
ncarson3
Contributor III
Contributor III

Month/Year vs Month/Prior Year

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.

1 Solution

Accepted Solutions
antoniotiman
Master III
Master III

Hi Neil,

see attachment

Regards,

Antonio

View solution in original post

9 Replies
Anil_Babu_Samineni

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

Here, What was the challenging you are facing?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
ncarson3
Contributor III
Contributor III
Author

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!

Anil_Babu_Samineni

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)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
ncarson3
Contributor III
Contributor III
Author

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)

Anil_Babu_Samineni

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
ncarson3
Contributor III
Contributor III
Author

Here is the sample you requested

avinashelite

Try like this ...

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

antoniotiman
Master III
Master III

Hi Neil,

see attachment

Regards,

Antonio

View solution in original post

ncarson3
Contributor III
Contributor III
Author

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