Solved

How to use equivalent of PREVIOUS() on dimension

  • 23 October 2023
  • 2 replies
  • 156 views
O
Rank
Badge

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 count
IF(Day.'Day of Week' = 'Day of Week"."Monday",
PREVIOUS(Day.Weeknumber)+1,
//IF NOT Monday, then keep current weeknum
PREVIOUS(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

O
Rank
Badge

Day Dimension

Issam Moalla
Rank
Userlevel 5
Badge +9

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)

        You can read more about this function and in the CUMULATE function documentation.

  • The ITEM() will retrieve the Weeknumber item based on the unique property in the Weeknumber dimension.
     
  • The TEXT() function was added if the unique property of your Weeknumber dimension has a type TEXT. 
    This function can be omitted if the unique property of the Weeknumber is number or integer type.
     

Let me know if this helps.

Best,
Issam

Reply


Ask your question here!

Terms & ConditionsCookie settings