11 Replies Latest reply on Aug 6, 2018 7:22 PM by Sonali chavan

# Rolling 4 and 12 weeks

Hi Guys,

I am struggling to achieve rolling 4 and 12 weeks from selected date:

I have written this formula:

IF [Parameters].[KPI]='Roll 4 week'

then

INT([Week Number]) <= DATEPART('week',[Date Selected])and

INT([Week Number]) >=    DATEPART('week', DATEADD('week',-3,[Date Selected]))

ELSEIF[Parameters].[KPI]='Roll 12 week' THEN

INT([Week Number]) <= DATEPART('week',[Date Selected])and

INT([Week Number]) >=    DATEPART('week', DATEADD('week',-11,[Date Selected]))

where week number is number of week.(1,2...52)

my formula fails when user selects 1st 3 weeks from start of the week.

Ex. When user selects 1 jan 2018 than it should give me 1, 52,51, and 50 week , same is the case with 2nd week (2,1,52,51)

Somehow i am not able to achieve this. Can someone please suggest something.

Regards,

Sonali.

• ###### 1. Re: Rolling 4 and 12 weeks

Hi, Sonali

Do you have any date type field other than week number in your data source?

ZZ

• ###### 2. Re: Rolling 4 and 12 weeks

Sonali , try this :

IF [Parameters].[KPI]='Roll 4 week'

then

INT([Week Number]) <= DATEPART('week',[Date Selected]) and

INT([Week Number]) >=    if DATEPART('week', [Date Selected])  <= 3 then DATEPART('week', [Date Selected]) - 3 + 52 else DATEPART('week', [Date Selected]) - 3 end

same for roll 12 week use [Date Selected]) <=11

• ###### 3. Re: Rolling 4 and 12 weeks

Hi Ankit,

Tried this formula but no luck. Shows black , Same as my formula. Can u please suggest something.

• ###### 4. Re: Rolling 4 and 12 weeks

Yes i have calendar date in my database and i have one date parameter for date selection.

• ###### 5. Re: Rolling 4 and 12 weeks

got it. Use this:

IF [Parameters].[KPI]='Roll 4 week'

then

(if DATEPART('week', [Date Selected])  <= 3

then

INT([Week Number]) <= DATEPART('week',[Date Selected]) OR

INT([Week Number]) >=  DATEPART('week', [Date Selected]) - 3 + 52

else

INT([Week Number]) <= DATEPART('week',[Date Selected]) and

INT([Week Number]) >=  DATEPART('week', [Date Selected]) - 3

end)

end

1 of 1 people found this helpful
• ###### 6. Re: Rolling 4 and 12 weeks

Hi Ankit,

Thank you So much. It Worked!!!!

Thanks,

Sonali.

• ###### 7. Re: Rolling 4 and 12 weeks

This did work for me correctly.

Can u suggest me how to order them like (1,52,51,50)

now we have something like: • ###### 8. Re: Rolling 4 and 12 weeks

Sonali,

Create one calculation field as:

IF [Parameters].[KPI]='Roll 4 week'

then

(if DATEPART('week', [Date Selected])  <= 3 and INT([Week Number]) >=50 then

INT([Week Number]) -52

else

INT([Week Number])

end)

end

then use this field to sort. You need not put this field on row or column shelf to sort your data view . You can sort existing weeknum  with this field by right clinking on weeknum pill and use sort by this new field, aggregate  u can use sum itself.

1 of 1 people found this helpful
• ###### 9. Re: Rolling 4 and 12 weeks

Hi Ankit,

it is working for 4 week. but not for 12 week. If I am selecting 2nd week then order should be, 2,1,52,51,50....

• ###### 10. Re: Rolling 4 and 12 weeks

I added this to formula :

IF  [Parameters].[KPI]='Roll 12 week'

then

(if DATEPART('week', [Date Selected])  <= 11 and INT([Week Number]) >=50 then

INT([Week Number]) -52

else

INT([Week Number])

end)

• ###### 11. Re: Rolling 4 and 12 weeks

for rolling 12 you need to change it as

IF  [Parameters].[KPI]='Roll 12 week'

then

(if DATEPART('week', [Date Selected])  <= 11 and INT([Week Number]) >=41 then

INT([Week Number]) -52

else

INT([Week Number])

end)

• ###### 12. Re: Rolling 4 and 12 weeks

Hello Ankit,

Above formula gives me what i want, but when in my year i have 52 weeks then then it wont give me 12 weeks.

If i modified this formula to (-52) then , when i have 53 weeks its giving me only 11 weeks.

Please help

Sonali