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

    Period Creation using calculated field

    Wesley Turner

      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