Positional Inventory

Options
awhite
awhite Member
in Charting & Analyzer
Domo Positional Inventory.PNG

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

  • GrantSmith
    GrantSmith Coach

    @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!**
  • awhite
    awhite Member

    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
    jaeW_at_Onyx Coach

    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"
  • awhite
    awhite Member

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

Categories