topic Re: Referencing Prior year against current year in QlikView

Referencing Prior year against current year

paulm — Thu, 23 Apr 2015 13:18:27 GMT

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

Thanks in advance

Paul

Re: Referencing Prior year against current year

sunny_talwar — Thu, 23 Apr 2015 13:22:14 GMT

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

Re: Referencing Prior year against current year

paulm — Thu, 23 Apr 2015 13:35:32 GMT

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

Re: Referencing Prior year against current year

sunny_talwar — Thu, 23 Apr 2015 13:42:17 GMT

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

Best,

Sunny

Re: Referencing Prior year against current year

jonathandienst — Thu, 23 Apr 2015 13:44:41 GMT

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

Re: Referencing Prior year against current year

sunny_talwar — Thu, 23 Apr 2015 13:44:44 GMT

Here is your chart in a straight table form.

Re: Referencing Prior year against current year

paulm — Thu, 23 Apr 2015 14:00:10 GMT

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?

Re: Referencing Prior year against current year

paulm — Thu, 23 Apr 2015 14:01:02 GMT

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

Re: Referencing Prior year against current year

sunny_talwar — Thu, 23 Apr 2015 14:13:22 GMT

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