Beastmode Count help

wgechter

Hello, I'm looking to create a beast mode to provide a summary number but my case statement counts are not working.  The data looks like this:

 

 Message Key     Data Source     

   1                         Collected

                              Delivered

   2                         Collected 

   3                         Collected

   3                         Collected

                              Delivered

 

Delivered never has a message key but collected could have duplicate message keys.  I'd like to say Count distinct meassage key if data source = Collected and SUM that together with count data source if data source = Delivered.   The total above would be 5( 2 delivered and 3 unique collected).  But I've tried many case statements with sum, with count distinct, count, etc and when I put them in the summary number, it turns up blank.  If I add it to a column in a table, the table gives me an error and says it can't be done.  This isn't that hard but something is wrong with my data or my beast mode since it's not working.  Thanks, Wendi  

ST_Superman

I think that you want to take the count function out of the case statements.  Try something like this:

 

COUNT(DISTINCT CASE WHEN `Data Source`='Collected' THEN `Message Key` END)

+

COUNT(CASE WHEN `Data Source` = 'Delivered' THEN `Data Source` END)