Ranking Week Number per Month

JosieNabarro
Occasional Contributor

Ranking Week Number per Month

Hi Everyone!

 

I have gotten a little stuck using the Rank Function and was wondering if someone would help me out.

 

Background:

 

I have a week dimensioned model.

 

I am trying to rank the dimensioned weeks to produce a resulting module that provides the week number within a calendar month. I would like the rank to reset every month

 

For example:

Week 1 of FY21 is the Week starting 03/10/2021 this would have a rank of 1

Week 6 of FY21 is the Week starting 07/11/2021 this would have a rank of 2

 

 

I have been able to dimension the module together, however when I use the rank function I just get the number 1 repeating.

 

Screenshot 2021-11-13 at 20.28.00.png

For reference my Rank line item is using the formula: RANK(Starting week of dimension)

 

I would appreciate any and all help 🙂

 

Have a lovely day

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisAHeathcote
Community Boss

Create a week time settings module dimensioned only by week.

Add a number formatted line item, call it 'Count'. Add 1 as a formula.

Create a month time settings module. 

Add a number formatted line item, call it number of weeks. Reference the 'Count' line item from the first module. 

Back to the weekly module.

Add a line item called, 'week number'. 

Use the following formula 

=If MONTHVALUE(MonthTimeSettings.NumberOfWeeks) <= PREVIOUS(WeekNumber)+1 THEN PREVIOUS (WeekNumber)+1 ELSE 1

 

This should Count the number of weeks each month and reset each time back to 1 when the Count equals the number of weeks in that month.

Chris
HeathcoteAndHerran.com

View solution in original post

2 REPLIES 2
ChrisAHeathcote
Community Boss

Create a week time settings module dimensioned only by week.

Add a number formatted line item, call it 'Count'. Add 1 as a formula.

Create a month time settings module. 

Add a number formatted line item, call it number of weeks. Reference the 'Count' line item from the first module. 

Back to the weekly module.

Add a line item called, 'week number'. 

Use the following formula 

=If MONTHVALUE(MonthTimeSettings.NumberOfWeeks) <= PREVIOUS(WeekNumber)+1 THEN PREVIOUS (WeekNumber)+1 ELSE 1

 

This should Count the number of weeks each month and reset each time back to 1 when the Count equals the number of weeks in that month.

Chris
HeathcoteAndHerran.com
JosieNabarro
Occasional Contributor

Thank you Chris!

 

As Always your help is much appreciated!