Unable to sum a beast mode formula

jrhessey · October 2022

I'm trying to get the first and last days of the selected date range and return dollar amounts on those days.

When I use the following formula I am able to sum the dollars

CASE WHEN `InventoryDate` = '10/1/2022' THEN `dollarsOnHand` ELSE 0 END

When I use the following formula I lose the ability to sum the column

CASE WHEN `InventoryDate` = MIN(MIN(`InventoryDate`)) OVER (PARTITION BY `PartNum`) THEN `dollarsOnHand` ELSE 0 END

I thought maybe a null value was the culprit, so I tried wrapping the formula in ifnull and return a 0 if null, but that didn't work either. Any ideas why I would lose the ability a aggregate with the min min value instead of the actual date value?

Thanks for any help!!

DavidChurchman · October 2022

There's no " over(partition by)" in BeastMode

I'm not a fixed function master, but I'm pretty sure you just need "BY". This user says FIXED BY is the same as ORDER(PARTITION BY)

https://dojo.domo.com/main/discussion/55689/fixed-functions-are-difficult-to-understand-and-should-be-rolled-into-window-functions

jrhessey · October 2022

The fixed function looks pretty sweet. I converted my formula to this

CASE WHEN `InventoryDate` = MIN(MIN(`InventoryDate`)) FIXED (BY `PartNum`) THEN `dollarsOnHand` ELSE 0 END

But I still don't get an aggregate option to sum the column.

Unable to sum a beast mode formula

Answers

Categories