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.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 621 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 743 Beast Mode
- 58 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
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive