• Replies 4 replies
  • Subscribers 961 subscribers
  • Views 126 views
  • Users 0 members are here
Related

formulas and null values

T.C. Brown
$organization

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.

Parents
  • $organization

    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

  • $organization in reply to Chris Wallingford

    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.

    Thanks.

    T.C.

Reply
  • $organization in reply to Chris Wallingford

    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.

    Thanks.

    T.C.

Children