% Sold of Available Inventory (aka SellThrough)
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 SellThrough 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% sellthrough
 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!!!
Best Answers

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.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.2 
@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.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**
1 
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.
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"2 
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/dataflowactions","data":[{"name":"runningTotals","id":"524fe3a494e345bc9a4d6a4a64b6b7b4","type":"WindowAction","gui":{"x":984,"y":108,"color":null,"colorSource":null},"dependsOn":["e6d736286753407088054b94904f79f2"],"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 sellthrough 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:
“There is a superhero in all of us, we just need the courage to put on the cape.” Superman1
Answers

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`)
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 
@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).
0 
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.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.2 
@MarkSnodgrass that's a very interesting idea  only problem is it breaks any date filtering :(
0 
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.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 
@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.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**
1 
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.
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"2 
@chapman  I'm not sure that I understand your sellthrough 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%
“There is a superhero in all of us, we just need the courage to put on the cape.” Superman1 
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 SellThrough: 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 sellthrough 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!!!
1 
@ST_Superman_ you're absolutely right  logic error on my end. Thank you for the correction!!
0 
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/dataflowactions","data":[{"name":"runningTotals","id":"524fe3a494e345bc9a4d6a4a64b6b7b4","type":"WindowAction","gui":{"x":984,"y":108,"color":null,"colorSource":null},"dependsOn":["e6d736286753407088054b94904f79f2"],"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 sellthrough 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:
“There is a superhero in all of us, we just need the courage to put on the cape.” Superman1 
@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 SellThrough 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!!!
1
Categories
 All Categories
 1.2K Product Ideas
 1.2K Ideas Exchange
 1.3K Connect
 1K Connectors
 267 Workbench
 Cloud Amplifier
 3 Federated
 2.5K Transform
 78 SQL DataFlows
 524 Datasets
 1.9K Magic ETL
 2.9K Visualize
 2.2K Charting
 433 Beast Mode
 22 Variables
 507 Automate
 113 Apps
 386 APIs & Domo Developer
 8 Workflows
 25 Predict
 9 Jupyter Workspaces
 16 R & Python Tiles
 331 Distribute
 76 Domo Everywhere
 255 Scheduled Reports
 65 Manage
 65 Governance & Security
 1 Product Release Questions
 Community Forums
 39 Getting Started
 25 Community Member Introductions
 67 Community Announcements
 4.8K Archive