Beastmode subtotal not calculating correctly

Anna_Otake

Hello,
I am trying to figure out what is wrong with my 3 beastmodes. One for this year's email sends, one for last year's email sends, and one for this year vs last year's difference.

Currently, the chart looks like this and the beast mode doesn't seem to be calculating correctly in each row plus the subtotal.

This Year Email Sends beast mode is:
CASE WHEN FY Order = 0 THEN COUNT(cm_pla) ELSE 0 END

LastYear Email Sends beast mode is:
CASE WHEN FY Order = 1 THEN COUNT(cm_pla) ELSE 0 END

This Year vs Last Year difference beast mode is:
((CASE WHEN FY Order = 0 THEN COUNT(cm_pla) ELSE 0 END)- (CASE WHEN FY Order = 1 THEN COUNT(cm_pla) ELSE 0 END))

If someone can help me look into this, I would appreciate it.

Thank you in advance!

MichelleH

@Anna_Otake You're definitely on the right track. The only changes I recommend are moving the COUNT outside the case statement and removing the ELSE 0 clause. Your formulas should all be formatted like this:

count(case when `FY Order` = 0 then `cm_pla` end)

MichelleH

Hi @Anna_Otake I'm glad that worked! If you want to count unique email types, just use COUNT(DISTINCT) instead of COUNT()

DomoDork

@Anna_Otake could you do a COUNT(DISTINCT LY Email Sends) to ensure your getting unique email sends and not total sends per day?

Edit: @MichelleH beat me to it :)

MichelleH

@Anna_Otake You're definitely on the right track. The only changes I recommend are moving the COUNT outside the case statement and removing the ELSE 0 clause. Your formulas should all be formatted like this:

count(case when `FY Order` = 0 then `cm_pla` end)

Anna_Otake

Hi @MichelleH, that worked perfectly! Thank you so much for your help!

Another thing I want to tweak is that I'm trying to count the number of email types and not the number of emails sent per week. For example, this one campaign has 11 emails sent to this campaign but all other days except 5/29 have been sent twice in one day. Is there a beast mode to consolidate and count this as one send? Essentially, all I want to achieve in this beast mode is to create a summary number of how many unique types of emails were sent every week.

MichelleH

Hi @Anna_Otake I'm glad that worked! If you want to count unique email types, just use COUNT(DISTINCT) instead of COUNT()

DomoDork

@Anna_Otake could you do a COUNT(DISTINCT LY Email Sends) to ensure your getting unique email sends and not total sends per day?

Edit: @MichelleH beat me to it :)

Anna_Otake

@MichelleH @DomoDork You two are awesome! That fixed it, thank you so much!