1 Reply Latest reply on Jul 11, 2018 12:56 PM by Mavis Liu

    Creating a calculated field for a date range

    Anjali Sn

      Hi,

       

      I want to be able to view data for the selected range of date and year. I have a YEAR filter, and then I have a range of date parameter like this:

       

      Screen Shot 2018-07-11 at 12.25.31 PM.png

       

      Screen Shot 2018-07-11 at 12.23.50 PM.png

       

      So, if 2017 year is selected and Jan-Jun is selected, I want to display data from 1/1/2017 to 6/1/2017, and like so for other selections.

       

      Now I created a calculated field for this purposes :

       

      IF STR([Year]) = '2017' AND [SixMonthsDateSelector] = 'Jan-Jun'

      THEN

      [Date] >= DATE('1/1/2017') AND [Date] <= DATE('6/1/2017')

      ELSEIF

      STR([Year]) = '2017' AND [SixMonthsDateSelector] = 'Jul-Dec'

      THEN

      [Date] >= DATE('7/1/2017') AND [Date] <= DATE('12/1/2017')

      ELSEIF

      STR([Year]) = '2018' AND [SixMonthsDateSelector] = 'Jul-Dec'

      THEN

      [Date] >= DATE('7/1/2018') AND [Date] <= DATE('12/1/2018')

      ELSEIF

      STR([Year]) = '2018' AND [SixMonthsDateSelector] = 'Jan-Jun'

      THEN

      [Date] >= DATE('1/1/2018') AND [Date] <= DATE('6/1/2018')

      END

       

      But it's not working as expected and is giving me "True" and "False" values instead of the dates. Can anyone tell me what is going on?

        • 1. Re: Creating a calculated field for a date range
          Mavis Liu

          HI Anjali,

           

          This is because your calculation is a boolean calculation and will return a true or false value if the statements fits or doesn't fit the criteria.

           

          You can use this as a filter instead, so bring it to your filters shelf and set it to true, and bring your Date field into the view.

           

          Your dates in the visualisation should only be showing dates which hit the 'true' criteria.

           

          Thanks,

           

          Mavis