Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
paulm
Contributor III
Contributor III

Referencing Prior year against current year

Hi,

Bit of a tricky one to explain but I have a chart with 3 expressions - revenue, (historic), budget (historic and future), Forecast (future) all against a single Year-week dimension e.g. 2014-01, 2014-02 etc.

Look looks perfect with a bar chart for  budget, and two lines for revenue and forecast.  The problem now we need to have a year on year comparison in the middle of the graph.

How can I get my chart to show Jan 2014 against Jan 2015?  In the pic below, red is budget, blue is revenue, green is forecast and black is the new line I want - 2014 revenue - but above 2015 dates

Capture.PNG

Thanks in advance

Paul

8 Replies
sunny_talwar

You will have to use Above function to achieve what you are trying to do. If there is a sample you can share, it would be easier to show then to explain.

Best,

Sunny

paulm
Contributor III
Contributor III
Author

Hi Sunindia,

Attached is an example of what I have.  I am looking for another line referencing the 2014 data, but it needs to show against 2015 like in the picture above.

Thanks
Paul

sunny_talwar

Have a look and see if this is what you are trying to achieve? PFA

Best,

Sunny

jonathandienst
Partner - Champion III
Partner - Champion III

Add a Year field and Week field during the load and then use these expressions:

     Sum({<Year = {'$(=Max(Year))'}>} Budget)  // for current/selectedyear

     Sum({<Year = {'$(=Max(Year) - 1)'}>} Budget)  // for prior year


Use Week for the dimension.


HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
sunny_talwar

Here is your chart in a straight table form.

Straight Table.PNG

paulm
Contributor III
Contributor III
Author

Hi Sunindia,

Basically perfect - one question though

1. When the user selects a single week, the previous year still shows for the full year.  Any idea how to limit it?

paulm
Contributor III
Contributor III
Author

Cheers for the reply Jonathan - the business needs a rolling 12/24 months so taking the year out is not feasible this time.

sunny_talwar

This expression will work if they only select one week. Is there a possibility that they may select multiple weeks as well??

If(GetSelectedCount([week Year]) <> 1, Above(sum({<[week Year] = >}Revenue), 52), Above(sum({<[week Year] = {"$(=Num((Left([week Year], 4) - 1) & (Right([week Year], 2))))"}>}Revenue), 52))



HTH

Best,

Sunny