Running Into Error with BeastMode and Table

I'm working on a beastmode where I am trying to assign a label to individual orders based on their price_tier
and their Product Name
.
If an order's price_tier
is equal to the MAX of the price_tier for a Product Name
, I want it labeled "Sold". If an order's price tier is less than the max, I want it labeled "Discount". And if its 0, I want it labeled "Comp".
I attached a data sample of what my dataset looks like. It's the first table in the attached file. I ended up using the script below, but I'm running to an error with my "Discount" fields.
"Sold/Comp/Discount"
CASE WHEN price_tier
= 0.00 then 'Comp'
WHEN price_tier
= MAX(price_tier
) OVER (PARTITION BY Product Name Consolidated
) then 'Sold'
WHEN price_tier
< MAX(price_tier
) OVER (PARTITION BY Product Name Consolidated
) AND price_tier
> 0 then 'Discount'
- - else 'Discount'
end
In the file, the lower table represents the output I get when I try to count the "Sold"/"Comp"/"Discount" for each product. Because I have two different price_tier
values under discount, they aren't aggregating like I want them too. Did I make an error in my beastmode? Or can someone help me understand why this is happening? I'm assuming it also might be the case that I just need to do this work in an ETL? But I'd like to know how it works.
Answers
-
I don't know why it isn't aggregating in the table view, but I do know that putting the logic in Magic ETL would solve the issue.
If I solved your problem, please select "yes" above
1
Categories
- All Categories
- Product Ideas
- 2.1K Ideas Exchange
- Data Connections
- 1.3K Connectors
- 309 Workbench
- 17 Cloud Integrations
- Data & ETL
- 2.3K Magic ETL
- 120 SQL DataFlows
- 666 Datasets
- Visualize & Apps
- 90 App Studio
- 198 Pro-code Components
- 2.6K Charting & Analyzer
- 873 Calculations & Variables (Beast Mode)
- AI & Data science
- 23 Domo AI & AI Chat
- 4 Managing AI
- 18 Jupyter Workspaces
- Automate
- 122 Workflows
- Alerts
- Distribute
- 118 Domo Everywhere
- 284 Reporting
- Manage
- 145 Governance & Security
- 489 APIs
- 11 Add-ins & Plugins
- 13 Domo Community Gallery
- 49 Product Releases
- 13 Domo University
- Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 118 Community Announcements
- 5K Archive