"difference to date" in an indicator widget

Nathanael Pearson I saw your note above about telling Analytics to "get today's date", were you able to dig that up? I'm trying to set up a function that will update some hardcoded benchmark goals on the first of the month in a contributions (i.e. if current month = 7, then return 6000, if current month = 8, then return 7000) and right now I only seem to be able to use date fields that are linked to contributions (which is not ideal, since with the overnight load and our gift processing timelines, the "current month" of the most recent contribution may be behind by a couple days). Appreciate any insight you might have!

Hi Amelia,

We could probably determine whether or not the the most recent contribution date is in the current month, comparing that date to NOW(), and if it's 0 (most recent contribution is in the current month), then return the goal for the given month, but if not, return a different goal... for example, starting simple, let's say it's just 1,000 increase to the goal each month as in your example: i.e. if current month = 7, then return 6000, if current month = 8, then return 7000.

CASE 
WHEN MAX( [Contribution Date, Calendar Month] ) = 7 THEN 6000
WHEN MAX( [Contribution Date, Calendar Month] ) = 8 THEN 7000
...etc...
ELSE 0 END
+
1000 * MIN( MDIFF( NOW( [Months in Contribution Date] ) , [Months in Contribution Date] ) 

If the latest contribution is in the current month, then this will add ( 1000 * 0 ) to the goal from the CASE statement. If instead we're in the month after the month of the latest contribution, it will add ( 1000 * 1 ) to the goal from the CASE statement. 

If the goals from month to month are irregular such that we can't simply add 1000 per month to get to the current month's goal, and let's assume that we'll never be more than 1 month ahead of the latest contribution date...

IF ( MIN( MDIFF( NOW( [Months in Contribution Date] ) , [Months in Contribution Date] ) = 0
,
CASE
WHEN MAX( [Contribution Date, Calendar Month] ) = 7 THEN 6000
WHEN MAX( [Contribution Date, Calendar Month] ) = 8 THEN 7000
...etc...
ELSE 0 END
,
CASE
WHEN MAX( [Contribution Date, Calendar Month] ) = 7 THEN 7000
WHEN MAX( [Contribution Date, Calendar Month] ) = 8 THEN 8000
...etc...
ELSE 0 END
)

If the latest contribution is in the current month, then use the first goal amount CASE statement, but if we've entered into the next month after the month of the latest contribution, then use a version of the CASE statement adjusted such that it returns the goal for the month after each given month.

As an aside, I reversed the dates in the DIFF function so that it would return a positive number. The DIFF functions (e.g. DDIFF([Date1],[Date2]) ) work like a timeline where Date2 is a fixed/starting point on the timeline, and the result is the number of periods and direction necessary to get to Date1. When NOW() is Date2, and Date1 is in the past, we have to move backward in time to get to the most recent contribution and the result will be negative. When the past contribution date is the fixed starting point, we have to move in the positive direction on the timeline to get to NOW(), and a positive number is returned. 

Thus also the use of MIN instead of MAX on the MDIFF result.

Best,
Chris

Hi Chris and Nathaneal,

Thank you SO much, this got lost in the shuffle of priorities, but I finally have some time to come back to this project and I so appreciate the advice!

This all makes complete sense conceptually, and the MDIFF function seems to be working as expected. I actually changed out Contribution Date for Create Date, in order to address some future dated contributions that were throwing things off, but I expect it should work the same.

I'm getting stuck with the WHEN statements, and in particular which dimensions I should be using for [Contribution Date, Calendar Month]. I keep either running into an issue where I keep getting 12 (because that is the MAX calendar month value) or an error. I may just be missing something glaringly obvious, but would you have any guidance on what dimensions I should be using the in the CASE WHEN statements to get the month of the MAX create date?

Hi Chris and Nathaneal,

Thank you SO much, this got lost in the shuffle of priorities, but I finally have some time to come back to this project and I so appreciate the advice!

This all makes complete sense conceptually, and the MDIFF function seems to be working as expected. I actually changed out Contribution Date for Create Date, in order to address some future dated contributions that were throwing things off, but I expect it should work the same.

I'm getting stuck with the WHEN statements, and in particular which dimensions I should be using for [Contribution Date, Calendar Month]. I keep either running into an issue where I keep getting 12 (because that is the MAX calendar month value) or an error. I may just be missing something glaringly obvious, but would you have any guidance on what dimensions I should be using the in the CASE WHEN statements to get the month of the MAX create date?

Hi Amelia,

I wasn't super clear, but I meant the "Calendar Month" field from the "Contribution Date" dimension... which I guess you'll use the "Create Date" dimension. 

monthlygoals.dash