7 Replies Latest reply on Jul 10, 2018 8:03 AM by Jim Dehner

# Fiscal Week calculation

My Fiscal Week ID values are as follow (for example):

....

201849

201850

201851

201852

201901

201902

201903

The following code is supposed to allow me to display 4 different metrics: Last Week Sales, Last 4 Week Sales, Last 13 Week Sales, and Year to Date Sales.

The only issue I am having is with the Last 4 Week Sales and Last 13 Week Sales. I am uncertain how to write the code to make it where it pulls both 2019 and 2018 values. As it is now, it takes the Maximum week and value and subtracts by whatever the integer is, which will not pull the prior year. So in this case, Last 4 Weeks and Last 13 Weeks only pulls the 2019 values (when we want it to pull them to fit the metric identification, so for example, Last 4 Weeks should pull 201903, 201902, 201901, and 201852).

CASE [Time Period]

when 1 THEN

IF [Max Week] = INT([Fiscal Week ID]) THEN

'True'

END

when 2 THEN

IF INT([Fiscal Week ID]) >= [Max Week] - 3

THEN

'True'

END

when 3 THEN

IF INT([Fiscal Week ID]) >= [Max Week] - 12

THEN

'True'

END

when 4 then

IF LEFT(STR([Fiscal Week ID]),4) = "2019"

THEN

'True'

END

END

Thanks!

• ###### 1. Re: Fiscal Week calculation

HI Michael

Could you please attach sample data (excel still work, but twbx is the best).

And your fiscal week rule (which day of week is the start of workweek (or normal calendar vs fiscal week reference tale is the best.)

Thanks,

Shin

• ###### 2. Re: Fiscal Week calculation

Hi Michael

what is the formula for Max Week?  something like {fixed : max(int(fiscal weeek id))}   ?

you are getting a match whenever the max week is not in play - so something is going on there

i would strip down the case statement to test each clause independently to make sure it returned what I expected

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 3. Re: Fiscal Week calculation

My apologies, I cannot because there is sensitive material.

Fiscal Week ID is actually set to being a measure.

There is another calculation "Maximum Week" with the following code: {FIXED:max([Fiscal Week ID])}.

Right now, Last Week Sales and Year to Date sales are correct. However, Last 4 Week Sales and Last 13 Week Sales are not. It's those two that I am uncertain how to rewrite the formula.

• ###### 4. Re: Fiscal Week calculation

since you cant show us more - I suggest you take the "Last 4 week " clause out of the case statement - convert it to an If statement and check each part of the statement to see what it is returning

Jim

• ###### 5. Re: Fiscal Week calculation

mmm,

Without understanding the logic or not seeing data, it's mission impossible..

Shin

• ###### 6. Re: Fiscal Week calculation

Does it help if I say that the returned values for the Last 4 Weeks and Last 13 Weeks return are the exact same as the Year to Date values?

As in, it takes the maximum fiscal week 201903, and stops at 201901 when computing--- and so it does not compute the 2018## values.

Sorry =/

• ###### 7. Re: Fiscal Week calculation

Michael

I understand

I think your max week has a 2019 date on it (if you have fiscal weeks in the bd in 2019 it will)

do this

and see what is returned

[  Max Week]

[Maw Week ]-3

and  INT([Fiscal Week ID]) >= [Max Week] - 3

with your fiscal week id on the row shelf - (unfiltered)  - does that return what you expect

Jim