Trying to figure out a conditional formatting formula for a Widget. I have the color formatting working with hard coded values, but I want to have different values with a CASE statement based on today's date and I just can't figure out the formula.
What I want:
Case
WHEN [today's date] < [fixed date I need to use] THEN [value]
WHEN [today's date] < [next fixed date] THEN [next value]
etc.
ELSE [final fixed value]
END
What I don't know how to do is return "today's date" in formula form for this test. Basically, I need the Analytics version of getdate() in SQL. Anyone know how to do this?
DJ
Hi David,
The NOW() function will get you today's date. Look for it in the functions list and it will have some instruction for you. So NOW( [Days in Whatever Date] ) will return today's date, regardless of whether there's data associated with that Whatever Date.
You'll find though that you can't do a date comparison using simple gt/lt operators. The CASE statement requires aggregate values and not dimensional members. To convert your logic into an aggregate value, DDIFF() to determine whether the count of days between the two dates is negative or positive to indicate which date is the earlier one.
One useful tip regarding that function, DDIFF ( [earlier date] , [later date] ) will return a negative count of days, while DDIFF ( [later date] , [earlier date] ) returns a positive count of days. Think of it as how many days do you add to the second date to get to the first date. So WHEN the count of days from NOW to Fixed Date is negative, then NOW is before Fixed Date:
WHEN MAX ( DDIFF ( NOW ( [Days in Whatever Date] ) , [fixed date you need to use] ) ) < 0 THEN [Value]
All that said, I'm hung up on getting you those fixed dates. We don't have a function to convert a string to a date... So, I suggest posting a fuller picture of what you're trying to accomplish and possibly move it into a support ticket to see if what you're trying to has alternative approaches.
Best,Chris