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.
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 ) )
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?
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))
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?
I think either that, or the formula under #2 in this comment would do the trick.