I have a requirement to show period over period with the change in % for different period intervals in different tables.
Week Ending Sun | | | | |
Week Period ending | Current Period Sales | Previous Period week | Prv. period Sales | Week over Week (%) |
2015-06-15 -- 06-21 | 1170 | 2015-06-08 -- 06-14 | 1100 | 6% |
2015-06-08 -- 06-14 | 1100 | 2015-06-01 -- 06-07 | 1050 | 5% |
2015-06-01 -- 06-07 | 1050 | 2015-05-25 -- 05-31 | 980 | 7% |
2015-05-25 -- 05-31 | 980 | 2015-05-18 -- 05-24 | 1020 | -4% |
2015-05-18 -- 05-24 | 1020 | 2015-05-11 -- 05-17 | 1040 | -2% |
2015-05-11 -- 05-17 | 1040 | 2015-05-04 -- 05-10 | 1050 | -1% |
2015-05-04 -- 05-10 | 1050 | 2015-04-27 -- 05-03 | 1000 | 5% |
| | | | |
Week ending Thu | | | | |
Week Period ending | Current Period Sales | Previous Period week | Prv period Sales | Week over Week (%) |
2015-06-19 -- 06-25 | 1140 | 2015-06-12 -- 06-18 | 1080 | 6% |
2015-06-12 -- 06-18 | 1080 | 2015-06-05 -- 06-11 | 1090 | -1% |
2015-06-05 -- 06-11 | 1090 | 2015-05-29 -- 06-04 | 990 | 10% |
2015-05-29 -- 06-04 | 990 | 2015-05-22 -- 05-28 | 1090 | -9% |
2015-05-22 -- 05-28 | 1090 | 2015-05-15 -- 05-21 | 1010 | 8% |
2015-05-15 -- 05-21 | 1010 | 2015-05-08 -- 05-14 | 1000 | 1% |
2015-05-08 -- 05-14 | 1000 | 2015-05-01 -- 05-07 | 1020 | -2% |
| | | | |
| | | | |
fiscal month | | | | |
# fiscal_month_period | Current Period Sales | Previous month Period | Prv period Sales | Month over Month (%) |
2015-07-01 -- 07-26 | 4560 | 2015-05-25 -- 06-30 | 5010 | -9% |
2015-05-25 -- 06-30 | 5010 | 2015-04-27 -- 05-24 | 4680 | 7% |
2015-04-27 -- 05-24 | 4680 | 2015-04-01 -- 04-26 | 3960 | 18% |
2015-04-01 -- 04-26 | 3960 | 2015-02-23 -- 03-31 | 4360 | -9% |
2015-02-23 -- 03-31 | 4360 | 2015-01-26 -- 02-22 | 4040 | 8% |
2015-01-26 -- 02-22 | 4040 | 2015-01-01 -- 01-25 | 4000 | 1% |
How can i accomplish this with the following data model
DIM - DATE
date_key -- key
cal_date -- calendar date
week_period_mon_sun_start_date -- corresponding mon-sun week start date of week
week_period_mon_sun_end_date -- corresponding mon-sun week end date of week
week_period_fri_thu_start_date-- corresponding fri - thu week start date of week
week_period_fi_thu_end_date-- corresponding fri - thu week end date of week
fiscal_month_start_date -- corresponding fiscal month start date of fiscal period
Fiscal_month_end_date -- corresponding fiscal month end date of fiscal period
calendar_month_start_date
calendar_month_end_date
FACT_SALES
date_key -- date dimension key
device_key -- device dimension key
...
order_count - count of sales for
order_amt - amount of sales
I have tried set analysis for prior period but it doesn't seem to work in the table. Something like shown below to subtract 7 day from the cal_date date column to get the previous 7 day date for any given period in the date dimension.
Sum( {< cal_date={$(=(cal_date -7))}>} sales)
What is the best way to model this in the data load and implement in the table? I have several metrics to be shown like this.
Thanks
SK
