5 Replies Latest reply on Oct 12, 2016 10:30 AM by Shinichiro Murakami

Period Creation using calculated field

I am attempting to create a a custom period variable using the following:

//2015 Creation

IF YEAR([Date])=2014 AND MONTH([Date])=12 and DAY([Date])>=14 THEN 1

ELSEIF YEAR([Date])=2014 AND MONTH([Date])=12 and DAY([Date])<=27 THEN 1

ELSEIF YEAR([Date])=2014 AND MONTH([Date])=12 and DAY([Date])>=28 THEN 2

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=1 and DAY([Date])<=10 THEN 2

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=1 and DAY([Date])>=11 THEN 3

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=1 and DAY([Date])<=24 THEN 3

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=1 and DAY([Date])>=25 THEN 4

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=2 and DAY([Date])<=7 THEN 4

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=2 AND DAY([Date])>=8 THEN 5

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=2 AND DAY([Date])<=21 THEN 5

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=2 and DAY([Date])>=22 THEN 6

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=3 and DAY([Date])<=7 THEN 6

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=3 and DAY([Date])>=8 THEN 7

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=3 and DAY([Date])<=21 THEN 7

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=3 and DAY([Date])>=22 THEN 8

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=4 and DAY([Date])<=4 THEN 8

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=4 AND DAY([Date])>=5 THEN 9

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=4 AND DAY([Date])<=18 THEN 9

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=4 and DAY([Date])>=19 THEN 10

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=5 and DAY([Date])<=2 THEN 10

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=5 and DAY([Date])>=3 THEN 11

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=5 and DAY([Date])<=16 THEN 11

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=5 and DAY([Date])>=17 THEN 12

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=5 and DAY([Date])<=30 THEN 12

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=5 AND DAY([Date])>=31 THEN 13

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=6 AND DAY([Date])<=13 THEN 13

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=6 and DAY([Date])>=14 THEN 14

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=6 and DAY([Date])<=27 THEN 14

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=6 and DAY([Date])>=28 THEN 15

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=7 and DAY([Date])<=11 THEN 15

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=7 and DAY([Date])>=12 THEN 16

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=7 and DAY([Date])<=25 THEN 16

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=7 AND DAY([Date])>=26 THEN 17

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=8 AND DAY([Date])<=8 THEN 17

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=8 and DAY([Date])>=9 THEN 18

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=8 and DAY([Date])<=22 THEN 18

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=8 and DAY([Date])>=23 THEN 19

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=9 and DAY([Date])<=5 THEN 19

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=9 and DAY([Date])>=6 THEN 20

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=9 and DAY([Date])<=19 THEN 20

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=9 AND DAY([Date])>=20 THEN 21

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=10 AND DAY([Date])<=3 THEN 21

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=10 and DAY([Date])>=4 THEN 22

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=10 and DAY([Date])<=17 THEN 22

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=10 and DAY([Date])>=18 THEN 23

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=10 and DAY([Date])<=31 THEN 23

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=11 and DAY([Date])>=1 THEN 24

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=11 and DAY([Date])<=14 THEN 24

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=11 AND DAY([Date])>14 THEN 25

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=11 AND DAY([Date])<29 THEN 25

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=11 and DAY([Date])>=29 THEN 26

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=12 and DAY([Date])<=12 THEN 26

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=12 and DAY([Date])>=13 THEN 27

ELSEIF YEAR([Date])=2015 AND MONTH([Date])=12 and DAY([Date])<=26 THEN 27

however when I check the data it puts data points into the wrong period or it does not create the field at all.

Please let me know if you have any ideas how to fix this issue.

Thank you

• 1. Re: Period Creation using calculated field

Wesley,

You SHOULD NOT create these kind of table with calculated field, there is NO merit at all.

You need to focus on understanding the logic of your fiscal calendar.

As long as I see, your period starts from Sunday.

Then 1 period consist of 2 weeks.

With these facts, I think your fiscal calendar seems to be close one to attached calendar.

I'm not sure, you can dig in the logic and confirm.

You can play around with parameter of "Fiscal 1/1 Adjustment".

Thanks,

Shin

[Fiscal Period]

int(([Fiscal Week]-1)/2)+1

[Fiscal Week]

int(

/7

)+1

1 of 1 people found this helpful
• 2. Re: Period Creation using calculated field

Shinichiro

This is so close to working however in 2015 my company has 27 pay periods all two weeks long. With what you have given me I am only able to get it to add one additional week to 2015 which causes 2016 to begin a week early do you have any ideas how i can fix this?

Also I am unable to show you what I have as my company is using 10.0.

• 3. Re: Period Creation using calculated field

Because I don't know the detail of your company calendar and we are just focusing on recent years.. I recommend that you use below formula combination, instead.

[FY start date]

if

[Date] <date("2014/12/14") then date("2000/1/1")

elseif [Date] <=date("2015/12/26") then date("2014/12/14")

else date("2015/12/27")

end

//

At this point, I ignore the date before "2014/12/14" and FY 2017

You can add more lines with your real calendar. I hope you can understand the logic easily.

//

[Fiscal  Two Weeks]

int(([Date]-[FY start date])/14)+1

And I don't understand your last comment, you can add any version file.

Thanks,

Shin

• 4. Re: Period Creation using calculated field

Shinichiro

Thank you for all your help. As for my last part of my last comment, I was told that older version would not be able to open files that were saved on newer versions.

Thank you again for all your help.

• 5. Re: Period Creation using calculated field

Sounds good.

Your understanding is correct, but people like me has >5 different versions of Tableau desktop, though still helpful if version is described in the post.

Thanks,

Shin