Positional Inventory

awhite · 2021-05-19T18:57:24+00:00

There was an error rendering this rich post.

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!

Find more posts tagged with

Accepted answers

All comments

GrantSmith

@awhite ,

How are you attempting to display this information? In a text card? A table card with multiple rows?

awhite

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!

jaeW_at_Onyx

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.

awhite

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