7 Replies Latest reply on Nov 1, 2017 11:02 AM by Avinash Suravarapu

Using IF,THEN,IFELSE Calculation for Dates

I am looking to use an IF,THEN,IFELSE Calculation to sort by dates. I have done this for Sums (i.e

IF

[Sum] < 0 THEN "0-286"

ELSEIF

[Sum] <= 287 THEN "287-573"

ELSEIF

[Sum] <= 574 THEN "574-860"

ELSEIF

[Sum] <= 8610 THEN "Everything Else"

END

I am looking to do the same premise but using [DATE]. Basically, IF [DATE] is before (less than) THEN Display as "Month-Month".  I tried achieving this by using Parameters, but it failed to execute as I wanted it to. I am assuming that the IF, ELSEIF can most likely only be applied to Sums, Profits etc...  I also looked into Bins for Dates but it does not display a range.

Thoughts?

• 1. Re: Using IF,THEN,IFELSE Calculation for Dates

Yes it's possible, you just have to wrap the dates in hashes

i.e.

IF [Date] < #2010-01-01# THEN 'Pre 2010'

ELSEIF [Date] < #2011-01-01 THEN '2010 to 2011'

...

END

Alternatively you can use MONTH([Date]) and look for > 1 (Jan), > 2 (Feb) etc.

7 of 7 people found this helpful
• 2. Re: Using IF,THEN,IFELSE Calculation for Dates

Thanks, worked perfectly!

• 3. Re: Using IF,THEN,IFELSE Calculation for Dates

How would I be able to do the same thing, but the have a calculated field be returned without a mixed aggregation error?

Ex)

IF [Date] < #2016-03-14# THEN

SUM([Total Revenue])/SUM([Actual Media Cost])

END

Thanks

1 of 1 people found this helpful
• 4. Re: Using IF,THEN,IFELSE Calculation for Dates

Samantha,

In order to do this you'd need to wrap your date in the 'ATTR' function like so;

IF ATTR([Date])  <  #2016-03-14#  THEN

SUM([Total Revenue])/SUM([Actual Media Cost])

END

Hope that helps!

• 5. Re: Using IF,THEN,IFELSE Calculation for Dates

Hello Mitchell,

Can you help me in below scenario, want to sum(Revenue) of current year, throwing an error message

Ex:

if DATEDIFF('year',[Promo Date],TODAY())=0 then Sum(Profit) END

• 6. Re: Using IF,THEN,IFELSE Calculation for Dates

Hey Kiran -

if year([Promo Date])=year(today()) then sum(Profit) end

Hope it helps.

• 7. Re: Using IF,THEN,IFELSE Calculation for Dates

May I please know how to compare date time? Something like this? Its not working for me.

IF [Date COL]) = #12/30/1899 00:02:00#

Thanks,

Avinash