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/2021this would have a rank of 1
Week 6 of FY21 is the Week starting 07/11/2021this 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.
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
Solved!去解决方案。
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
这应该算我们的数量eks each month and reset each time back to 1 when the Count equals the number of weeks in that month.
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
这应该算我们的数量eks each month and reset each time back to 1 when the Count equals the number of weeks in that month.
Thank you Chris!
As Always your help is much appreciated!