Incorrect calculation when computing the value using case statement

Hi,
The below beast mode in DOMO gives correct results when only 'metric A' is used as a filter in domo.
When 'metric A' and 'metric B' is selected as a filter, the computed result goes a bit off.
I have attached screenshots of the result.
Note: The numbers changes for 5th Dec'22. Correct value is 72224.29 when used only 'metric A', in case of 2 metrics in filter the result changes to 72213.86
(case when `metrics`=`metric A` then (case when ((`values` - lag(`values`,1) OVER (ORDER BY `date_id`))=0 and `date_id`>'2022-10-01' ) then (((lag(`values`,1) OVER (ORDER BY `date_id`)) - (lag(`values`,7) OVER (ORDER BY `date_id`)))/7 + `values`) else `values` END ) end)
Answers
-
I wonder if adding metric B is messing with your lag function. I'm speculating... But is it possible that just because you're filtering on Metric A in your first case statement, it doesn't necessarily create a new table that excludes B? That would mess with your lag calc because it's at the row level wouldn't it?
Edit -- I wonder if @chetan_cricut I wonder if you put an order by in your first case statement if that would resolve???
0
Categories
- 10.6K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 477 Transform
- 1.8K Magic ETL
- 69 SQL DataFlows
- 478 Datasets
- 218 Visualize
- 260 Beast Mode
- 2.1K Charting
- 12 Variables
- 19 Automate
- 356 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 249 Distribute
- 65 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 191 Product Ideas
- 1.2K Ideas Exchange
- 11 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive