Last value in a given time period

I'm trying to write a beast mode to give me the last value depending on how the time data are grouped (e.g. by week, then value on last day of each week; by month, then value on last day of each month). I believe I could do this in magic ETL but then the end user can no longer choose the time grouping they're interested in dynamically on the card.
The columns are: [member_count, date] with example values like: [100, 2024-01-15]
In the current format of the data there might not be values for every single day in a timeframe (e.g., 2024-01-16 might be missing). I could probably fill in every date if it would help.
I tried doing something like: CASE WHEN date = MAX(date) THEN member_count ELSE 0 END
But then I wasn't able to aggregate that beast mode - I think MAX(date) may not be the right choice. Thanks for any help!
Answers
-
I've done this with a DataSet that was grouped by month, so that it could be changed to by quarter or year and still be getting only the most recent value of those time groupings and not duplicate. I imagine the same concept can be used by day too. I achieved that with the following formula:
(CASE
WHEN Month = MAX(MAX(Date) FIXED (REMOVE Month, Relationship Status, Repurchase Type)) THEN 'Yes'
ELSE 'No'
END)The Date column is already grouped by month in the DataFlow. The Month column is just an exact duplicate of the Date column, but it has to be added in the DataFlow, not on the card. Then, just add the Beast Mode as a filter on the card and select Yes. You'll want to have Month (or in your case Day) in the REMOVE part of the FIXED function. The other columns are any other column that you want to have as a series in the chart.
Was this comment helpful? Click Agree or Like below.
Did this comment solve your problem? Accept it as the solution!0
Categories
- All Categories
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 310 Workbench
- 7 Cloud Amplifier
- 9 Federated
- 3K Transform
- 114 SQL DataFlows
- 654 Datasets
- 2.2K Magic ETL
- 4K Visualize
- 2.5K Charting
- 797 Beast Mode
- 78 App Studio
- 44 Variables
- 757 Automate
- 188 Apps
- 480 APIs & Domo Developer
- 72 Workflows
- 17 DomoAI
- 40 Predict
- 17 Jupyter Workspaces
- 23 R & Python Tiles
- 408 Distribute
- 119 Domo Everywhere
- 279 Scheduled Reports
- 10 Software Integrations
- 141 Manage
- 137 Governance & Security
- 8 Domo Community Gallery
- 47 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 114 Community Announcements
- 4.8K Archive