Max Case Statement

Reply
Highlighted
Yellow Belt

Max Case Statement

Hello!

 

I would like to create a beastmode that will calculate the case fill percentage for the most current fiscal week I have in my data.

 

For example, if I have fiscal weeks 1-45 in my data, I would like the calculate the case fill percentage for week 45 only. Each week I will receive new data, so next week when I get week 46, I would like the card to automatically calculate the case fill % for week 46 rather then having to manually change the fiscal week by using  filter in the card. I cannot use the date range filter to pull 'current week' or even 'previous week' because we receive new data on Thursday's so when we roll over to the new week the date filter will be incorrect. 

 

I  thought the below beastmode would work, but it does not seem to pull any data

 

(CASE
when
`Fiscal Calendar Week` = MAX(`Fiscal Calendar Week`)
then
(SUM(`Original Order Cases`)- SUM(`Cut`)) / SUM(`Original Order Cases`)
end)


Accepted Solutions
Highlighted
Major Red Belt

It's because you don't have the week number in your comparison so anything that isn't 0 is treated as true.

 

Try this:

 

(CASE
when
MAX(MAX(`Fiscal Week`)) OVER () = `Fiscal Week`
then
(SUM(`Original Order Cases`)- SUM(`Cut`)) / SUM(`Original Order Cases`)
end)



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**

View solution in original post


All Replies
Highlighted
Major Red Belt

Assuming you only have a single year's financial data in your dataset (not spanning years) you could try a window function which would get you the highest week number across your entire dataset:

 

```

MAX(MAX(`Fiscal Calendar Week`)) OVER ()

```

 

instead of 

```

MAX(`Fiscal Calendar Week`)

```



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**
Highlighted
Major Red Belt

Also, to clarify Window functions are a feature switch. If you don't have them enabled in your instance talk with your CSM to get them turned on.



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**
Highlighted
Yellow Belt

Thank you for the reply! I changed my formula to the below, but it is returning the average of all the weeks rather than just the most recent week

 

(CASE
when
MAX(MAX(`Fiscal Week`)) OVER ()
then
(SUM(`Original Order Cases`)- SUM(`Cut`)) / SUM(`Original Order Cases`)
end)

Highlighted
Major Red Belt

It's because you don't have the week number in your comparison so anything that isn't 0 is treated as true.

 

Try this:

 

(CASE
when
MAX(MAX(`Fiscal Week`)) OVER () = `Fiscal Week`
then
(SUM(`Original Order Cases`)- SUM(`Cut`)) / SUM(`Original Order Cases`)
end)



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**

View solution in original post

Highlighted
Yellow Belt

Thank you so much!!

Announcements
Domopalooza 2021 Call for Presenters: We want to hear how Domo is revolutionizing the way you do business!

Click here to submit your story.