Hi. I am trying to create a widget for a challenge match we have that began Dec. 1. I have created formulas to calculate the base amount as well as the amount to be credited as the match. I noticed that when there is no donation amount in one of the columns, the formulas aren't calculating correctly. For instance, to find the base amount I have:
AC20
([Total Amount], [Campaign])
Campaign filtering on last year's campaign
AC21 Q1
([Total Amount], [Campaign], [Days in Date])
Campaign filtering on this year's campaign
Days in Date filtering for any donation less than or equal to Nov 30
I made the above favorite formulas and used those in another formula:
Base Amount
If( [ AC20] > [AC21 Q1] , [AC20] - [AC21 Q1] , 0 )
The Base Amount formula calculates correctly for those who have a donation in both columns. But I noticed that it is not correct for donors, who didn't give in one of the segments and I suspect it is because there are null values. For donors who gave last year, but not in the first quarter of this year, the base amount is 0, rather than their donation last year. I thought if I could make columns where there is no donation show a zero, maybe the formulas would work. But I haven't hit on anything that works. I'm not sure if I'm on the right track, and wondered if anyone has any suggestions.
Thanks.
T.C.
Hi T.C.
Typically any conditional investigation involving null yields a false result. For example, in SQL, this statement yields a 0 result.
SELECT CASE WHEN 1 > NULL THEN 1 ELSE 0 END
So in this case, try something like
CASE WHEN ISNULL([AC21 Q1]) THEN [AC20]WHEN [AC20] > [AC21 Q1] THEN [AC20] - [AC21 Q1]ELSE 0 END
Best,Chris
Hi Chris,
This worked for when there was no contribution in the first quarter. For donors who don't have a donation in either AC21 Q1 or AC20, the formula doesn't show 0.
I was trying to add to the example you gave with
CASE WHEN ISNULL([AC21 Q1]) AND ISNULL( [AC20]) THEN 0 WHEN ISNULL([AC21 Q1]) THEN [AC20] WHEN [AC20] > [AC21 Q1] THEN [AC20] - [AC21 Q1] ELSE 0 END
But it doesn't populate 0 in the column for the Base Amount, and wondered if I was missing something.
I'm guessing there are some specifics about the dashboard/widget filters at play in what you're experiencing with the formulas' nulls. Would you mind opening a support ticket and attaching an export of your dashboard, please?
Hi Chris. I have opened a ticket and attached the dashboard.