Using FIXED () and Variable?
I'd like to create a BeastMode 'MTD %
' that can:
- Calculate the % of each GL account based of Total Sales
- 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
Answers
-
How about
CASE
WHEN `Fiscal Year` = `Year Selector`
AND `Month Selector` = `Month Name`
THEN
SUM(`Amount`)
/
SUM(CASE WHEN `Class 3` = 'Sales' THEN `Amount` ELSE 0 END)
END** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
I tried, and I got this error
0 -
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:SUM(
CASE
WHEN `Fiscal Year` = `Year Selector`
AND `Month Selector` = `Month Name`
AND `Class 3` = 'Sales'
THEN `Amount`
ELSE 0
END
)MTD %:
CASE
WHEN SUM(
CASE
WHEN `Fiscal Year` = `Year Selector`
AND `Month Selector` = `Month Name`
AND `Class 3` = 'Sales'
THEN `Amount`
ELSE 0
END
) = 0 THEN NULL
ELSE
SUM(
CASE
WHEN `Fiscal Year` = `Year Selector`
AND `Month Selector` = `Month Name`
THEN `Amount`
ELSE 0
END
)
/
SUM(
CASE
WHEN `Fiscal Year` = `Year Selector`
AND `Month Selector` = `Month Name`
AND `Class 3` = 'Sales'
THEN `Amount`
ELSE 0
END
)
END** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
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.0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive