Another table has the day and the equivalent day of previous year. Fi: 2012 October 10 is equivalent to 2011 October 14; 2012 September 28 is equivalent to 2011 September 30 and so on.
When I want to know sales from one period of time, some days one month, etc vs the previous year I solved it with set analysis and concat; it works with city, region, store, brand, etc. But with Day as dimension it doesn’t work
How can I get this table?:
Sales previous year
In this table, first row, Sales previous year should be the sales of 2011 September 30 because is the equivalent day of 2012 September 28
A set expression is evaluated once per chart, not per dimension value. But you would need to look up the corresponding DAY_PREV_YEAR per DAY value. So I don't think set analysis is the way to go here.
I still suggest considering a script based solution. My first post should just give you an idea on how you can maybe do this. This idea results in having a new field [Sales previous year] in your sales table.
Using joins is probably not the best approach when you are coping with large data sets. But you can achieve the same with lookup /mapping tables. Please refer to
Not sure why the first approach (I assume the mapping load) should affect your runtime performance. The idea is that you get the same data model than with my first reply (using left joins). But I now think the CROSSTABLE solution is probably better, anyway.