Dynamic Dates Week Comparison

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:

mceclip0.png

 

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.

mceclip1.png

 

The output from this will look like this (notice the WeekYear is different from the actual CalendarYear):

mceclip2.png

 

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:

mceclip3.png

 

Full date key on Day Of Week Name attribute:

mceclip4.png

 

Combined key on Week Number attribute:

mceclip5.png

 

Attribute relationships:

mceclip6.png

 

 

Decorations: Dynamic Dates Definition

Use the ‘X’ year (WeekYear) in the right places:

mceclip7.png

mceclip8.png

 

Comparison: Week to Yesterday – This year and Last year

Set up your comparison like this:

2021-05-03_14-04-06.png

 

To get a result like this:

mceclip10.png

Was this article helpful?
3 out of 3 found this helpful

Comments

0 comments

Please sign in to leave a comment.