Best Of

@ST_-Superman-_ I love the approach -- prevents me from needing to UNION my data, which doubles my rows. There must be some dimension in my actual full dataset that is not playing nicely with the solution because, at first pass, I get SUMs of Sell-Through and TotalStartingInventory -- 7,495% and 2,326,995 respectively, where the numbers should be around 6.9% and 1.5M.

I'll take another crack with fresh eyes and see if I can figure out what I'm doing wrong.

Either way - marking yours as an answer because it definitely works in the smaller dataset. Thanks very much for your response!!!

Spent the better part of the day working on this and have a solution thanks to @MarkSnodgrass!! UNION was the answer here.

I was able to pass a date field into the inventory table and combine it with the new FIXED function to get the total inventory per store, per period with filters working as expected.

This returns the cumulative orderQty by day: SUM(SUM(`orderQty`)) OVER (ORDER BY `orderDate`)

This returns the total inventory available: SUM(SUM(`qtyAvail`) FIXED ())

And then I combine the two to get Sell-Through: SUM(SUM(`orderQty`)) OVER (ORDER BY `orderDate`) / SUM(SUM(`qtyAvail`) FIXED ())

@RobSomers had a novel idea but it broke when using filters. When calculating the sell-through on each row in ETL, I had to choose what the total inventory number was -- and it can vary depending on the current filters (store/region/etc.). I really wanted this idea to work so I didn't have to UNION and double my dataset but alas. Marking as an answer because it does work depending on your dataset/need to filter.

@jaeW_at_Onyx thank you for expanding on Mark's solution - the addition of activity_type is great for documentation. In this case, I found the FIXED function worked a little better than a window because it allows me to get the total for the entire [filtered] dataset without worrying about the dates I chose in the inventory data. I am finding reasons to use FIXED almost every day.

Thank you all very much for your awesome help!!!

consider using window functions to get the desired result.

if you UNION your data (Consumption UNION to Inventory with a column activity_type = "consumption" or "inventory" so you can tell them apart) as @MarkSnodgrass recommended and structure your data such that you have Activity_Date and Activity_YearMonth,

you could construct your metric as a window function

sum(sum(consumption_amount)) over (partition by item, activity_yearMonth order by activity_date asc) -- this will give you the cumulative sum of your consumption for the month.

sum(sum(inventory)) over (partition by item, activity_yearMonth order by activity_date asc) will give you available inventory.

you can either choose to include activity_yearMonth in the partition clause or exclude depending on if you want the counter to reset every month.

@chapman Not sure if this will work 100% or not, but it's a crazy idea I just had. In your ETL, after your join could you just do a formula that calculates for each row what the percentage is? Then in your card, just do a sum of the percentages? Ex for Store A:

(1/50)+(1/50)+(1/50)+(3/50)= 6/50 = 12%

is the same as:

(1+1+1+3)/50 = 6/50 = 12%

Hopefully I remember enough about math that I'm not being wildly incorrect. I think if you're just doing a simple sum, it should work.

Do you not have a date in your inventory table? You can always add a date in the etl process, such as the first day of the month. There are several different ways you can derive a date dynamically depending on your data. You can give it the same column name as your orders table date column so that the filtering will work.

A second option is to not join your data in the ETL, but union your data with the append rows tile. Choose include all columns when appending. This will keep your inventory rows from repeating and then you should be able to use SUM(orderqty) / SUM(qtyavail) to get the percent sold.

You can get around the fact that the qtyAvail is repeating by using the average function in your beast mode like this:

SUM(orderQty) / AVG(`qtyAvail`)