Best Of

@charles , typically we'll see users have Current Month and Previous Month as column titles.

you could still adopt @MichelleH 's suggestion if you limit your dataset via filters to the "last x months" then you can still display months in your columns, and the data filter restricts you to the correct set of months.

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

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.