% Sold of Available Inventory (aka Sell-Through)

chapman

I am stumped on this one.

I have two tables: an orders table and an inventory table. orders contains a record for each order placed by a customer; and inventory holds a record of how much inventory is available at the start of the period. Note that qtyAvail does not update... it is a reflection of how much that store started with. Once it's gone, it's gone.

I want to calculate Sell-Through in a Beast Mode. SUM(orderQty) / `qtyAvail`

The issue is that I need to plot by orderDate and I need the number to be cumulative. In the screenshot is an example.

• Looking at Store A in my example ----
  • As of 2/21 they had 50 available and 1 sold = 2% sell-through
  • As of 2/23 they sold 1 more for a total 2 sold (of 50 available) = 4%
  • etc. etc.

By the end, I want to see the total % of inventory sold, filterable by Store (and also with a total for All Stores). I cannot, for the life of me, figure out how to join the inventory table to the orders table. I can join on store but then I end up with the total qtyAvail repeating on every line and sums are overstated.

Thanks in advance!!!

MarkSnodgrass

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.

RobSomers

@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.

jaeW_at_Onyx

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.

ST_Superman

@chapman

I think we can get this to work if you can add a running total column to your dataflow. If you are using ETL2.0 than you can copy and paste the code below to show you an example.

{"contentType":"domo/dataflow-actions","data":[{"name":"runningTotals","id":"524fe3a4-94e3-45bc-9a4d-6a4a64b6b7b4","type":"WindowAction","gui":{"x":984,"y":108,"color":null,"colorSource":null},"dependsOn":["e6d73628-6753-4070-8805-4b94904f79f2"],"removeByDefault":false,"notes":[],"partitionedAggregation":false,"additions":[{"name":"totalOrdersToDate","operation":{"type":"FRAMED","operationType":"SUM","column":"orderQty","preceding":null,"following":0}}],"orderRules":[{"column":"orderDate","caseSensitive":false,"ascending":true}],"groupRules":[{"column":"Store","caseSensitive":false},{"column":"Product","caseSensitive":false}]}]}

Once you have the running total for OrderQty and the Qty Available joined to your order data, the table should look something like this:

You can see that totalOrdersToDate is a running total by store of the orderQty field. Also, Total Starting Inventory is repeated for every line of data for a store in that period.

Now, to calculate Sell Through:

sum(`totalOrdersToDate`) / sum(AVG(`Initial_Inventory_Qty`) FIXED (BY `Store`))

I think your table had some calculation errors for the sell-through values. (I forgot, I also added another formula to show the TotalStartingInventory in the table above )

sum(AVG(`Initial_Inventory_Qty`) fixed (by `Store`))

Or, you could just show the sell through by date:

MarkSnodgrass

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`)

chapman

@MarkSnodgrass that was my gut reaction too but that doesn't work when I'm looking at multiple/all stores -- I get the average (55 in the example) rather than the total quantity available (110 in the example).

MarkSnodgrass

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.

chapman

@MarkSnodgrass that's a very interesting idea - only problem is it breaks any date filtering :(

MarkSnodgrass

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.

RobSomers

@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.

jaeW_at_Onyx

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.

ST_Superman

@chapman - I'm not sure that I understand your sell-through table. For 2/21 you show that store A sold through 2% of its inventory (1/50) and Store B sold 0% (0/60). However, for All Stores you say 2%... shouldn't the calculation be (1/110)? 0.9%

chapman

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

chapman

@ST_-Superman-_ you're absolutely right -- logic error on my end. Thank you for the correction!!

ST_Superman

@chapman

I think we can get this to work if you can add a running total column to your dataflow. If you are using ETL2.0 than you can copy and paste the code below to show you an example.

{"contentType":"domo/dataflow-actions","data":[{"name":"runningTotals","id":"524fe3a4-94e3-45bc-9a4d-6a4a64b6b7b4","type":"WindowAction","gui":{"x":984,"y":108,"color":null,"colorSource":null},"dependsOn":["e6d73628-6753-4070-8805-4b94904f79f2"],"removeByDefault":false,"notes":[],"partitionedAggregation":false,"additions":[{"name":"totalOrdersToDate","operation":{"type":"FRAMED","operationType":"SUM","column":"orderQty","preceding":null,"following":0}}],"orderRules":[{"column":"orderDate","caseSensitive":false,"ascending":true}],"groupRules":[{"column":"Store","caseSensitive":false},{"column":"Product","caseSensitive":false}]}]}

Once you have the running total for OrderQty and the Qty Available joined to your order data, the table should look something like this:

You can see that totalOrdersToDate is a running total by store of the orderQty field. Also, Total Starting Inventory is repeated for every line of data for a store in that period.

Now, to calculate Sell Through:

sum(`totalOrdersToDate`) / sum(AVG(`Initial_Inventory_Qty`) FIXED (BY `Store`))

I think your table had some calculation errors for the sell-through values. (I forgot, I also added another formula to show the TotalStartingInventory in the table above )

sum(AVG(`Initial_Inventory_Qty`) fixed (by `Store`))

Or, you could just show the sell through by date:

chapman

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