Incorrect calculation when computing the value using case statement

chetan_cricut
chetan_cricut Member
edited March 2023 in Beast Mode

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

metric A and B.PNG


only metric A.PNG 
(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

  • G_Grey
    G_Grey Contributor
    edited January 2023

    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???

Categories