Current Year v Prev Year

[richard_simpson]

I have a model with a lovely star-schema, with facts in the middle surrounded by various dimension tables just one association away...

The dimensions are tables like: Calendar, Customer, Employee, Product etc.

I want to display a chart which sums values from the FACT table, showing figures for this year and last year (alongside each other).

If I set the dimension to be Month (Jan, Feb, Mar, Apr etc.) then of course it will work fine.

But if I want the chart to show more than 12 months then I need to add the Year to the Month Name...

e.g.   Oct13, Nov13, Dec13, Jan14, Feb14, Mar14, Apr14, May14, Jun14, Jul14, Aug14, Sep14, Oct14, Nov14, Dec14

If I didn't add the "Year" suffix, then values for Oct13 and Oct14 would appear together under "Oct".

However, by adding year, I now of course find that when I try to show 2 columns, one with This Year values and alongside it another for Prev Year values... Oct This Year appears against Oct14, and Prev Year values appear against Oct13 (because that is how they are joined to the calendar!)

I can think of some complicated model changes to get what I want... but there must surely be an easy way of getting the desired result by expressions in the chart?

Can anyone help?

Thanks

[vgutkovsky]

Richard, if you already have 2 expressions (This Year and Previous Year), then you don't need Year as a dimension.

Vlad

[richard_simpson]

Hi Vlad

Thanks for your reply / suggestion...

I do have expressions for ThisYear and PrevYear, but what I want to do is visualise this information by each calendar month when there are figures for ThisYear (and sales for this year began more than 12 months ago), and against these show the equivalent values for PrevYear (where sales started 12 months earlier, and again, went on for more than 12 months)

e.g.

Sales for 2014: Began selling in advance of the start of the year. Tickets are for a particular month in 2014, but went on sale in March 2013. So sales for 2014 (any month) began in March 2013 and will go on until Dec 2014

Sales for 2013 have now finished (they finished in Dec 2013) but they began in March 2012.

If the 1st month of a particular year is represented by T1, and the last month T12, then tickets for that year started selling in T-8 (March Year Before) and stopped selling in T13 (Jan Year after).

So I suppose I want to show sales for 2014, in months T-8 to T12, compared to sales in equivalent months for Prev Year.

Hope that makes sense?

[vgutkovsky]

I think I understand. Sounds like maybe you're hooking into the wrong date. Why don't you just connect a calendar to the ticket event date rather than the ticket sale date? So your chart dimension would be EventMonth, and your expressions would be ThisYear and PrevYear. Would that give you what you need or am I still missing something?

Regards,

Vlad

[MarcoARaymundo]

try this way

[richard_simpson]

Hi Marco

Thanks for responding... unfortunately I can't see what you mean by "this way"?

[richard_simpson]

Hi Vlad

Thanks, but that won't give the desired result.

Imagine you organise a concert or event each year.

You always stage the event in the 1st week of August.

But you start selling tickets 18 months in advance. So Feb - Jul each year you are selling tickets for both the concert that is being staged in August the same year, and also in August the following year.

Given the above, I want to show a monthly breakdown of ticket sales for a concert (over the full 18 month period that tickets were on sale) with a comparison to ticket sales for the previous year's concert, for the equivalent 18 month period that those tickets were on sale.

Does that clarify it?

[vgutkovsky]

Richard, that does clarify it, thank you. However, I still think that the last solution that I suggested would work for your purposes. The only thing that I'm assuming is that you are, in fact, capturing 2 dates in your Fact record: (1) ticket sale date, and (2) concert event date. If that's the case, then you should be fine with the solution I mentioned (linking the calendar to the event date). I'm attaching an example that should give you an idea of what I mean.

Cheers,

Vlad