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

# Creating a calculated field for a date range

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:

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

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