Unable to sum a beast mode formula
I'm trying to get the first and last days of the selected date range and return dollar amounts on those days.
When I use the following formula I am able to sum the dollars
CASE WHEN `InventoryDate` = '10/1/2022' THEN `dollarsOnHand` ELSE 0 END
When I use the following formula I lose the ability to sum the column
CASE WHEN `InventoryDate` = MIN(MIN(`InventoryDate`)) OVER (PARTITION BY `PartNum`) THEN `dollarsOnHand` ELSE 0 END
I thought maybe a null value was the culprit, so I tried wrapping the formula in ifnull and return a 0 if null, but that didn't work either. Any ideas why I would lose the ability a aggregate with the min min value instead of the actual date value?
Thanks for any help!!
Answers
-
There's no " over(partition by)" in BeastMode
I'm not a fixed function master, but I'm pretty sure you just need "BY". This user says FIXED BY is the same as ORDER(PARTITION BY)
0 -
The fixed function looks pretty sweet. I converted my formula to this
CASE WHEN `InventoryDate` = MIN(MIN(`InventoryDate`)) FIXED (BY `PartNum`) THEN `dollarsOnHand` ELSE 0 END
But I still don't get an aggregate option to sum the column.
0
Categories
- 10.5K All Categories
- 4 Connect
- 914 Connectors
- 250 Workbench
- 458 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 476 Datasets
- 183 Visualize
- 249 Beast Mode
- 2.1K Charting
- 11 Variables
- 16 Automate
- 354 APIs & Domo Developer
- 88 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 245 Distribute
- 62 Domo Everywhere
- 242 Scheduled Reports
- 20 Manage
- 41 Governance & Security
- 169 Product Ideas
- 1.2K Ideas Exchange
- 10 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive