In order to handle dynamic weeks correctly, you must take into consideration that some weeks, weeks 53 and 1, often will span across two years.
As in this example, where 30th and 31st of December 2019 actually belongs to Week 1 of 2020:
For something like this to work correctly, you will need to consider these things (explained in detail below):
- Your Source table for your time dimension, the Period table, must contain WeekYear information - e.g. ISO_Week_YearNumber #WYYY.
- Your time dimension must use this WeekYear information - as well being setup correctly with combined keys and attribute relationships.
- Your Dynamic Dates definition (in Decorations) must use the WeekYear settings - e.g. [<XXXX>].&[<X]&[W>].&[<YYYY-MM-DD>]
Source table from Period Table Task
Your source table for your time dimension needs to contain the special ISO Week-Year info. TARGIT’s Period Table Task can handle this for you.
The output from this will look like this (notice the WeekYear is different from the actual CalendarYear):
Period dimension with YWD Hierarchy
When designing the Period dimension from this source table, you must consider correct attribute relationship and combined keys on the week level.
Dimension structure:
Full date key on Day Of Week Name attribute:
Combined key on Week Number attribute:
Attribute relationships:
Decorations: Dynamic Dates Definition
Use the ‘X’ year (WeekYear) in the right places:
Comparison: Week to Yesterday – This year and Last year
Set up your comparison like this:
To get a result like this:
Comments
Please sign in to leave a comment.