• Replies 5 replies
  • Subscribers 961 subscribers
  • Views 63 views
  • Users 0 members are here
Related

$0 gifts vs NULL gifts

Nathanael Pearson
$organization

I was tinkering on one of my dashboards and noticed something peculiar. 

I was trying to see the number of orders WITHOUT a contribution, which I figured would be as simple as saying "[Count (ORDER ID)] , [Amount = $0]). That, however, returns the same number as the [# of Unique Order IDs], which shouldn't happen. It seems to be having some cognitive dissonance by saying "The number of total orders (without any considerations) is X. The number of total orders with a gift attached is Y (smaller than X). The number of Total orders without a gift is X". Those things can't all be true.

I tried to say "count the orders where the Gift Amount is NULL", but it wouldn't take that with bucketing. I tried to get fancy with the following formula:

SUM ( [Order ID] ,
IF (
( ISNULL([Total Contribution Amount]) )
, 1
, NULL
)
)

But that returns Null in the widget:

When I switch it to 'COUNT (Order ID)...' it returns 0.

I could take the formula I think works (# of orders , contribution > 0) and subtract it from the total order count, but there has to be a reason why the logic works like I'm seeing.

  1. How can the statements "The number of total orders (without any considerations) is X. The number of total orders with a gift attached is Y (smaller than X). The number of Total orders without a gift is X." be all true?
  2. How can I most cleanly craft a formula that asks "How many order DID NOT have a contribution on them?" 
Parents
  • $organization

    Hi Nathanael,

    1. Within every Order ID exists rows with a Contribution Amount = 0 and you're intended to return Order ID where not exists any row with a Contribution Amount > 0, or where the aggregation of all rows within the Order ID = 0.

    2. A quick way might be the total number of orders minus the number of orders that DO contain a contribution. However, in wanting to validate that...

    SUM ([Order ID],
      IF( [Total Contribution Amount] = 0
      ,1,0
      )
     )
  • $organization in reply to Chris Wallingford

    Thanks, Chris. In this case, am I incorrect in thinking about NULL vs 0? 

    Why would the number of orders with contribution = 0 be the same as the number of orders, if we know that some orders have contribution > 0?

  • $organization in reply to Nathanael Pearson

    The total count of orders with a contribution amount = 0 is the same as the total number of orders because every order contains a row with contribution amount = 0 exists within every order, even in cases where the SUM() of the contribution amount within such an order is > 0.

    In the formula you posted (below), since the contribution amount in this scenario is never null, only ever 0, NULL is being returned from the IF statement for every order.

    SUM ( [Order ID] ,
    IF (
    ( ISNULL([Total Contribution Amount]) )
    , 1
    , NULL
    )
    )

  • $organization in reply to Chris Wallingford

    Oh, interesting. I looked back at your screenshot and got a bit more clarity. In this case, the only way to answer the question "How many orders DID NOT have a gift" would be to subtract the number of orders where [Contribution Amount] > 0 from the total number of orders. Is that right?

Reply Children