Dynamic Positional Inventory

andyRowan

Hi,

I am looking to create a pivot table that is able to handle positional inventory dynamically.

For example, if I have 3 weeks in my filtered range, I want to only show the data on the last week.

I am using the below.

CASE
WHEN Week ending date = MAX(Week ending date) OVER ()
THEN SUM(ifnull(EOH Qty,0))
ELSE 0
END

This works if I have all my dimensions in the pivot table.

When I remove elements or weeks I get errors although the totals are correct,

The totals are still correct and even the line level is okay but I am still seeing ### in one row.

If I am only showing store level totals are correct but it isn't summing the values by store.

Anyone ever try to do this?

andyRowan

Inventory Test (1).xlsx

This is the supporting dataset

DavidChurchman

With that OVER() clause in your CASE statement, it's producing a value for each week that can't be combined. If you have the week as part of the Row/Column definition, then you're fine. If you don't, then there are collisions.

The way your function is written will always give 0s to everything besides this week, so is there a reason to include past weeks in the card? If not, you could filter the table for just this week, which will prevent the collisions. A dynamic filter BeastMode could look like this:

CASE
WHEN Week ending date = MAX(Week ending date) OVER () then 'Include'
else 'Exclude'
END

(Card filter Include)

Otherwise, you'll need to approach your BOH and EOH functions differently, so that they are able to aggregate across weeks and not create collisions.

andyRowan

Yes so I will also have sales in my dataset and I will need to create calculations for available which will take my full month's sales and add them to the ending position of the month.

The table will look like the below

DavidChurchman

It's really hard to get partition or fixed functions to play well with a Pivot Table. You could create something that checks against an input variable, where the user can input which week they want to calculate BOH with (less automatic then what you were exploring):

case
when Week ending date >= date(Date Input Variable) then BOH Qty
else 0
end

(Choose the SUM aggregation after bringing it into the value).

—

If your data is updated daily/weekly, you could maybe do something more dynamic with CURRDATE:

case

when Week ending date >= CURRDATE() - 7 then BOH Qty

else 0

end

— Not sure about that -7, depending on your update cadence.

andyRowan

Thank you, if I created a date variable to select the proper week ending date of the timeframe how would the bestmode look to make it usable?