Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
Anonymous
Not applicable
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)

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
Anonymous
Not applicable
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?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
Anonymous
Not applicable
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

Anonymous
Not applicable
Author

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