Beast Mode

Beast Mode

Using FIXED () and Variable?

Member
edited October 2024 in Beast Mode

I'd like to create a BeastMode 'MTD %' that can:

  1. Calculate the % of each GL account based of Total Sales
  2. Can be dynamically filtered by Month and Year variables

Here's my MTD % Beast Mode:

SUM(`Amount`)/ (SUM(CASE WHEN `Class 3` = 'Sales' THEN `Amount` ELSE 0 END) FIXED ())

And my MTD Beast Mode:
SUM(CASE
WHEN Fiscal Year= Year Selector AND Month Selector = Month Name THEN (Amount*-1)
ELSE 0
END)

I just need to incorporate the MTD in the MTD % but it didn't work

Tagged:

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In

Answers

  • How about

    1. CASE
    2. WHEN `Fiscal Year` = `Year Selector`
    3. AND `Month Selector` = `Month Name`
    4. THEN
    5. SUM(`Amount`)
    6. /
    7. SUM(CASE WHEN `Class 3` = 'Sales' THEN `Amount` ELSE 0 END)
    8. END

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • I tried, and I got this error

  • I didn't realize this was on a pivot table. Yes, the calculations must be full aggregated with the case logic contained in the aggregate. I don't have a sample dataset to test with. How about this?

    MTD:

    1. SUM(
    2. CASE
    3. WHEN `Fiscal Year` = `Year Selector`
    4. AND `Month Selector` = `Month Name`
    5. AND `Class 3` = 'Sales'
    6. THEN `Amount`
    7. ELSE 0
    8. END
    9. )

    MTD %:

    1. CASE
    2. WHEN SUM(
    3. CASE
    4. WHEN `Fiscal Year` = `Year Selector`
    5. AND `Month Selector` = `Month Name`
    6. AND `Class 3` = 'Sales'
    7. THEN `Amount`
    8. ELSE 0
    9. END
    10. ) = 0 THEN NULL
    11. ELSE
    12. SUM(
    13. CASE
    14. WHEN `Fiscal Year` = `Year Selector`
    15. AND `Month Selector` = `Month Name`
    16. THEN `Amount`
    17. ELSE 0
    18. END
    19. )
    20. /
    21. SUM(
    22. CASE
    23. WHEN `Fiscal Year` = `Year Selector`
    24. AND `Month Selector` = `Month Name`
    25. AND `Class 3` = 'Sales'
    26. THEN `Amount`
    27. ELSE 0
    28. END
    29. )
    30. END

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • Member
    edited November 2024

    Thank you for the response, yes I can provide a sample dataset with random numbers:

    For my Pivot Table, I just dragged Class 1, 2, 3 into Rows, and MTD, MTD % Beast Modes into values

    I tried the new MTD Beastmode above, I was only able to returned the Sales Amount and not other GL Account Amount, hence the MTD % won't work because it's not gonna be able to calculate the % of Sales for other.

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In