Beastmode subtotal not calculating correctly

Options
Anna_Otake
Anna_Otake Member
in Beast Mode

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.

image.png


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!

Tagged:

Best Answers

  • MichelleH
    MichelleH Coach
    Answer ✓
    Options

    @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
    MichelleH Coach
    Answer ✓
    Options

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

  • DomoDork
    DomoDork Contributor
    edited June 2023 Answer ✓
    Options

    @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 :)

Answers

  • MichelleH
    MichelleH Coach
    Answer ✓
    Options

    @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
    Anna_Otake Member
    edited June 2023
    Options

    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.

    image.png

  • MichelleH
    MichelleH Coach
    Answer ✓
    Options

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

  • DomoDork
    DomoDork Contributor
    edited June 2023 Answer ✓
    Options

    @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
    Anna_Otake Member
    Options

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

Categories