Solved

How to use equivalent of PREVIOUS() on dimension

• 2 replies
• 105 views

• New Pigmenteer
• 2 replies

Hi!

I want to add “Weeknumber” to my “Day” dimension.
Since WEEKNUM() is not available, I want to use the equivalent of SHIFT/PREVIOUS to calculate the weeknumber.

I have a dimension called Weeknumber (1-53), and would like to do something along the following line, in order map Days → Weeknumbers:

``//Check if first day of year, IF(LEFT(Day.'dd-MM-YYYY',5)="01-01" ,ITEM(1,Weeknumber.Week),// Else: check if workday = Monday then add 1 to week countIF(Day.'Day of Week' = 'Day of Week"."Monday",PREVIOUS(Day.Weeknumber)+1,//IF NOT Monday, then keep current weeknumPREVIOUS(Day.Weeknumber))``

However, both PREVIOUS and SHIFT does not seem to work. Is there a workaround?

icon

Best answer by Issam Moalla 26 October 2023, 18:25

View original

2 replies

Day Dimension

Userlevel 3
+1

Hello OyvindB,

Could you try the following formula in the property of the day dimension:

``ITEM(TEXT(CUMULATE(IF(Day.'Day of Week'.Name = "Monday", 1), Day,Day.Year)),WEEKNUM)``

This formula can be broken down as follows:

• the CUMULATE function will attribute the week number to each day and resets each Year as it is used as the grouping dimension:
``CUMULATE(IF(Day.'Day of Week'.Name = "Monday", 1), Day,Day.Year)``