3 Replies Latest reply on Feb 25, 2014 8:17 AM by Gabriel Queiroz

# Create a calculated field to choose between two dates sets

I would like to create a calculated field with a conditional clause to choose between two sets using an ordinary parameter.

Although, when I try to do that, I got the message that I can't create the condition as the set is already a boolean calculation.

Here is what I already did:

Set 1

Dates from Jan,1,2012 to Dec,31,2013

Set 2

Dates from Jan, 1, 2013 to Dec, 31, 2014

Please, note that the 2013 year must need to be a common year for both sets.

The user will choose between the periods of 2012-2013 or 2013-2014.

I already tried another work around like making dateparts with the years, however I am not able to use 2013 two times, I mean, it get a valid calculated field, however when I select the second part (2013-2014) it only shows me the 2014 because the 2013 is already "in use" by the 2012-2013 condition. Please, look my first try below that illustrates this case:

IF [Date] >= #1/1/2012 AND [Date] <= #12/31/2013 THEN "2012-2013"

ELSEIF [Date] >= #1/1/2013 AND [Date] <= #12/31/2014 THEN "2013-2014"

END

Any ideas?

Thanks in advance. Gabriel

• ###### 1. Re: Create a calculated field to choose between two dates sets

Yes. I did it.

Here is the solution:

CALCULATED FIELD FOR 2012-2013 (name 2012-2013 Period)

IF DATEPART('year',[Date]) != 2014 THEN "2012-2013"

END

CALCULATED FIELD FOR 2013-2014 (name 2013-2014 Period)

IF DATEPART('year',[Date]) != 2012 THEN "2013-2014"

END

CREATE A PARAMETER (name Period)

integer, 1 for 2012-2013 and 2 for 2013-2014

CALCULATED FIELD TO VALIDATE PARAMETER (name Valid Period)

IF [Period] = 1 THEN [2012-2013] ELSE [2013-2014] END

INCLUDE THE CALCULATED FIELD [Valid Period] in the filter

Choose to exclude NULL

That's it!!!!!!!!!!!!!!!

• ###### 2. Re: Create a calculated field to choose between two dates sets

Thanks for posting your solution, and marking the answer "correct".  Glad you got it working--cheers!

• ###### 3. Re: Create a calculated field to choose between two dates sets

Thank you Matthew.

The good part of this is that it can be used for many dates aggregation as you want, specially aggregations that uses the same part of the date more then one time.

For example, if a client has a fiscal year from July to June and also would like to see the data in the ordinary year format (Jan to Dec) it can be done using the logical above.

Best. Gabriel