Referencing Prior year against current year

[paulm]

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

[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]

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

[sunny_talwar]

Here is your chart in a straight table form.

[paulm]

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]

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