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
- 7.3K All Categories
- 13 Getting Started in the Community
- 141 Beastmode & Analytics
- 1.8K Data Platform & Data Science
- 54 Domo Everywhere
- 2K Charting
- 1K Ideas Exchange
- 903 Connectors
- 236 Workbench
- 342 APIs
- 77 Apps
- 19 Governance & Productivity
- 234 Use Cases & Best Practices
- 50 News
- 473 Onboarding
- 572 日本支部