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 145 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)
Best Answer

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 Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Answers

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 Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 
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 Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 
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)0 
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 Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 
Thank you so much!!
0 
Hi I have a similar question.
I used the suggested MAX formula to compare 2 years. I have 5 year data and need to compare them by using a filter.
With below BM, it looks like it returns the right numbers for they are at the right columns:
Recent Year: case when MAX(MAX(`Year`)) OVER () = `Year` then sum(`Gross Revenue`) end
Previous year: case when Max(Max(`Year`)) OVER () > `Year` then sum(`Gross Revenue`) end
My question is why if I remove the year column, both year total is at recent year. Both recent and previous should be in their respective column and in the same row. Please advise. Thanks you in advance for your help.
0 
When using Window Functions anything that's part of the ORDER BY or PARTITION BY clause must be included on an axis (or SORT BY) clause in analyzer.
uh... this looks a bit janky to my eyes and certainly you have to be careful how you use this CASE statement.
case when MAX(MAX(`Year`)) OVER () = `Year` then sum(`Gross Revenue`) end
What are you trying to accomplish?
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0
Categories
 All Categories
 1.8K Product Ideas
 1.8K Ideas Exchange
 1.5K Connect
 1.2K Connectors
 298 Workbench
 6 Cloud Amplifier
 8 Federated
 2.9K Transform
 100 SQL DataFlows
 616 Datasets
 2.2K Magic ETL
 3.8K Visualize
 2.5K Charting
 729 Beast Mode
 54 App Studio
 40 Variables
 678 Automate
 173 Apps
 451 APIs & Domo Developer
 46 Workflows
 8 DomoAI
 34 Predict
 14 Jupyter Workspaces
 20 R & Python Tiles
 394 Distribute
 113 Domo Everywhere
 275 Scheduled Reports
 6 Software Integrations
 121 Manage
 118 Governance & Security
 Domo Community Gallery
 33 Product Releases
 10 Domo University
 5.4K Community Forums
 40 Getting Started
 30 Community Member Introductions
 108 Community Announcements
 4.8K Archive