Positional Inventory
Hi,
I am looking to build a beast mode calculation to give me the sum of my data based on the max date currently in my card.
So if I have the card at the day level I show the sum of inventory position of that day. If I have the card at the week level I show the sum of inventory position of my last day within the time frame.
This formula seems to give me the date value correctly but I can't get the actual positional value of a column on the date dictated by this formula.
MAX(MAX(`PoPDate`)) OVER (PARTITION BY DAY(`PoPDate`),WEEK(`PoPDate`),YEAR(`PoPDate`))
Thanks!
Answers
-
@awhite ,
How are you attempting to display this information? In a text card? A table card with multiple rows?
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
Hi Grant,
I am hoping to display in a table card with the ability to be flexible between graphs and mega/pivot tables. If I am showing a daily view I would love to be able to transition to a weekly or monthly view without changing the beast mode formula.
Thanks!
0 -
You're probably not going to get away from showing the value on the last day AND dynamically changing the date grain of the viz without restructuring your data.
If it were me, I would APPEND the data for each of the Date grains you're interested in, then add a constant "Date Grain" that switches to "by Day", "by Week", "by Month"
Then write a row_number() instead of a Max(Max()) over (). You're assuming inventory only goes up. And it could go down.
so row_number() over (Partition by <date grain> order by date desc ) in the dataset view or as a materialized column would get the job done.
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 -
Thank you so much for your response!!
We were trying to say is to say give me the sum of the inventory value on the max date whether the value goes up or down.
Sorry I am totally new to Domo so I'm a tad confused on the approach.
Would I be creating an inventory table and appending it to my sales rather then having inventory columns? I'm not 100% sure on how it would be structured since I have all the data at the UPC level and I could group by product category and or location.
Would the row_number formula be the formula that pulls off these inventory columns? So like row_number('inventory value column') over (Partition by 'date grain column' order by date desc )
or would I have 4 row_number formulas with one for each patrician? Would I sum this column to group by store or department?
Sorry for all the questions!!
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 60 App Studio
- 41 Variables
- 689 Automate
- 177 Apps
- 454 APIs & Domo Developer
- 48 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 397 Distribute
- 114 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 127 Manage
- 124 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive