8 Replies Latest reply on Oct 23, 2018 7:18 AM by Melody H

# Flexible Bi-weekly formula

Hi guys,

Currently I have a bi-weekly formula like this:

'WK '+

STR({ FIXED FLOOR( DATEPART('week',[Date],'Monday')/2):MIN(DATEPART('week',[Date],'Monday'))}) +

" & " +

STR({ FIXED FLOOR( DATEPART('week',[Date],'Monday')/2):MAX(DATEPART('week',[Date],'Monday'))})

but in my tableau report it shows double week 30.

The filter/sets that I use is last N weeks (In this case i took last 4 bi-weekly) and excluding today's week (So week 31 is not included).

So instead of the combination of (Wk24&25 - Wk26&27 - Wk28&29 - Wk30&30), I want the combination of (Wk23&24 - Wk25&26 - Wk27&28 - Wk29&30).

• ###### 1. Re: Flexible Bi-weekly formula

Hi Melody,

You are using floor its causing the issue

In the place of FLOOR use round(datepart('week',order date)/2,0)  it will work correctly

Hope this helps Kindly mark this answer as correct and helpful so that it will help  others

BR,

NB

• ###### 2. Re: Flexible Bi-weekly formula

Thanks Naveen!

• ###### 4. Re: Flexible Bi-weekly formula

Hi Naveen,

I still have a question regarding this. I changed to ROUND, but this week it shows double week 31. Do you know why? it fixed after i changed it again to FLOOR...

Thanks

• ###### 5. Re: Flexible Bi-weekly formula

Hi Melody,

Could you please attach the sample workbook to check overall data once

BR,

NB

• ###### 6. Re: Flexible Bi-weekly formula

Hi,

Please find attached the tableau report (and the data source just in case if you can't open the tableau without it).

Regards,

M

• ###### 7. Re: Flexible Bi-weekly formula

Hi Melody,

It seems for Odd and even weeks  need flip the formula with Round and float

Formula to USE:

IF (DATEPART('week',DATETRUNC('week', TODAY(),'Monday')))%2=0 THEN

'WK '+

STR({ FIXED FLOOR( DATEPART('week',[London Date],'Monday')/2):MIN(DATEPART('week',[London Date],'Monday'))}) +

" & " +

STR({ FIXED FLOOR( DATEPART('week',[London Date],'Monday')/2):MAX(DATEPART('week',[London Date],'Monday'))})

ELSE

'WK '+

STR({ FIXED ROUND( DATEPART('week',[London Date],'Monday')/2):MIN(DATEPART('week',[London Date],'Monday'))}) +

" & " +

STR({ FIXED ROUND( DATEPART('week',[London Date],'Monday')/2):MAX(DATEPART('week',[London Date],'Monday'))})

END

Hope this helps

BR,

NB

• ###### 8. Re: Flexible Bi-weekly formula

Hi,

Thank you for always helping me solving Tableau!

I have an extended question related to this formula. I wanted to make it per 4 weekly and i tried to modify the formula like this but the grouping doesn't seem to be ok.

IF [Date]<DATETRUNC('week', TODAY(),'monday') and(DATEPART('week',DATETRUNC('week', TODAY(),'Monday')))%4=0 THEN

'WK '+

STR({ FIXED FLOOR( DATEPART('week',[Date],'Monday')/4):MIN(DATEPART('week',[Date],'Monday'))}) +

" & " +

STR({ FIXED FLOOR( DATEPART('week',[Date],'Monday')/4):MAX(DATEPART('week',[Date],'Monday'))})

ELSEIF [Date]<DATETRUNC('week', TODAY(),'monday') THEN

'WK '+

STR({ FIXED ROUND( DATEPART('week',[Date],'Monday')/4):MIN(DATEPART('week',[Date],'Monday'))}) +

" & " +

STR({ FIXED ROUND( DATEPART('week',[Date],'Monday')/4):MAX(DATEPART('week',[Date],'Monday'))})

END

Now is week 43 and indeed week 43 (22 - 28 oct) is excluded (based on the formula) but I want the grouping to be per 4 weeks instead of week 42 making its own category (2018 - WK 42 & Wk 45). Do you know how to solve this? Thanks in advance