Rolling 12 weeks dynamic display as columns in a cross tab

[Former Member]

Good day Folks,
I have an issue creating rolling 12 weeks displaying dynamically as columns in a cross tab.
 
I have 1 product dimension with values Desktop, Laptop, Tablet, Phone and a measure Sales.

I want to create a cross tab with product dimension in the rows and rolling 12 weeks starting from last week until previous 12 weeks as columns and corresponding sales in the data intersection. please see attached  "rolling 12 weeks.png"

For us week is Monday to Sunday, so previous week would be Last Monday to this Sunday, for example today date is 20 December, so last week should be "Dec 09- Dec 15" followed by "Dec 02-Dec 08" then its previous week until 12 weeks back.

Since I don't have a central date dimension table with weeks, do I have to create past 12 weeks as 12 rows populating dynamically in a derived table in universe and use the column , drag drop in the column bar of the cross tab which breaks the sales to the corresponding product in the

corresponding week ?  or any other alternative ? please let me know.

Any help would be greatly appreciated.
 
-Sateesh

[Former Member · Accepted solution]

The textbook answer is probably the one you don't want to here - create the date dimension table.

How are you planning on determining week number without one?

[Former Member · Accepted solution]

That's correct Mark, ideally we should have a date table. But we don't.

So I've created 12 individuals columns with 12 different formula's for every past week as below.

Last week:

=([Sales Revenue]) Where ([Sales Date] Between (RelativeDate(LastDayOfWeek(CurrentDate());-13);RelativeDate(LastDayOfWeek(CurrentDate());-7)) )

Last to Last week:

=([Sales Revenue]) Where ([Sales Date] Between (RelativeDate(LastDayOfWeek(CurrentDate());-20);RelativeDate(LastDayOfWeek(CurrentDate());-14)) )

and so on until 12 weeks and  that worked.