How to aggregate a case statement that uses a fixed function
Hello,
I was hoping somebody could help me understand how to sum the following case statement.
case when `Date` = max(max(`Date`)) fixed () then `On Hand by Day - DO NOT SUM` else 0 end
I need the calculation to be dynamic and reference the date selected on the card. All attempts to aggregate the case statement fail and the analyzer does not offer any aggregation options.
Thanks in advance.
Chris
** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**
Answers
-
@Chris_Wolman You'll likely have more luck with this if you identify the max date in a dataflow instead of using a fixed function since you're trying to aggregate based on the result of another aggregation. If you create a dataset field called `Max Date` in your dataflow, then the beast mode would look like this:
sum(case when `Date` = `Max Date` then `On Hand by Day - DO NOT SUM` else 0 end)
0 -
@MichelleH how do I identify the max date selected on the card in the dataflow? This calculation needs to reflect the max date of the selected date on the card and calculate dynamically. The user may want to see the past 30 days or maybe the previous 3 months or any other range? I need to be able to select the on hand value as of the max date selected. Ultimately, I want to calculate a percentage using this value but I cannot get the first value correct.
Thank you.
Chris
** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**0 -
Did you figure out how to do this?
0 -
@Kpbp we ended up utilizing variables to reference the user selected periods.
Chris
** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 292 Workbench
- 4 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 95 SQL DataFlows
- 602 Datasets
- 2.1K Magic ETL
- 3.7K Visualize
- 2.4K Charting
- 691 Beast Mode
- 43 App Studio
- 39 Variables
- 658 Automate
- 170 Apps
- 441 APIs & Domo Developer
- 42 Workflows
- 5 DomoAI
- 32 Predict
- 12 Jupyter Workspaces
- 20 R & Python Tiles
- 386 Distribute
- 111 Domo Everywhere
- 269 Scheduled Reports
- 6 Software Integrations
- 112 Manage
- 109 Governance & Security
- 8 Domo University
- 30 Product Releases
- Community Forums
- 39 Getting Started
- 29 Community Member Introductions
- 98 Community Announcements
- Domo Community Gallery
- 4.8K Archive