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
- All Categories
- 1.1K Product Ideas
- 1.1K Ideas Exchange
- 1.2K Connect
- 966 Connectors
- 254 Workbench
- Cloud Amplifier
- 1 Federated
- 2.4K Transform
- 75 SQL DataFlows
- 499 Datasets
- 1.8K Magic ETL
- 2.7K Visualize
- 2.2K Charting
- 360 Beast Mode
- 19 Variables
- 483 Automate
- 101 Apps
- 378 APIs & Domo Developer
- 6 Workflows
- 22 Predict
- 6 Jupyter Workspaces
- 16 R & Python Tiles
- 316 Distribute
- 64 Domo Everywhere
- 252 Scheduled Reports
- 59 Manage
- 59 Governance & Security
- 1 Product Release Questions
- 5K Community Forums
- 37 Getting Started
- 23 Community Member Introductions
- 63 Community Announcements
- 4.8K Archive