Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
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

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

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

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

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