Need to run a loop for each row of table

[dhanopiyaankit]

Hi All,
I need to do a calculation “Count of Months to utilize Stock” in a table in Quicksight. “Count of Months to utilize Stock” is the months taken to utilize all the stock available for each month as shown in the Result Table.
Explanation of the data in “Result table”:

Date : Date aggregated on month level
Stock: Stock or inventory available in the given month
Sales Forecast: Forecast of the sales for each month. This field will be used to identify the “Count of Months to utilize Stock”
“Count of Months to utilize Stock”: It is the months taken to utilize all the stock available for each month.
For Example: “Count of Months to utilize Stock” for Jan month: (30000-(123137+105118+69543)) in this cal stock will be finished in 3 months so “Count of Months to utilize Stock” is 3
Feb month: 128 - (105118+69543) in this cal stock will be finished in 3 months so “Count of Months to utilize Stock” is 3

I am attaching an excel that contain the Result table and a table to show how to calculate the “Count of Months to utilize Stock”. Please take a look and let me know if you need more information.

[sagmukhe]

@dhanopiyaankit - This is an interesting problem and I don’t have any good optimized solution for you at the moment in case you would like to do it natively in QuickSight. However, since I loved your problem statement, wanted to share an potential crude implementation to you which I was playing around with.

See the Above Snapshot and Months to Server is the final metric I believe what you are looking for. Sharing some of the calculations below for your reference as well.

Jan Sales :

sumover(sumIf({ Stock}, Date = “01/01/2023”), [ ], POST_AGG_FILTER)

Jan Cumulative Sales :

runningSum(sum({ Sales Forecast}), [Date ASC])

Feb Cumulative Sales :

runningSum(sum(ifelse(Date < “02/01/2023”, 0, { Sales Forecast})), [Date ASC])

Mar Cumulative Sales :

runningSum(sum(ifelse(Date < “03/01/2023”, 0, { Sales Forecast})), [Date ASC])

Apr Cumulative Sales :

runningSum(sum(ifelse(Date < “04/01/2023”, 0, { Sales Forecast})), [Date ASC])

Note : Please see the pattern in the computation logic

Final Metric → Months to Serve :

ifelse(
max(Date) = “01/01/2023”, sumOver(ifelse({Jan Sales} > {Jan Cumulative Sales}, 1, 0), [ ], POST_AGG_FILTER),
max(Date) = “02/01/2023”, sumOver(ifelse({Jan Sales} > {Feb Cumulative Sales}, 1, 0), [ ], POST_AGG_FILTER),
max(Date) = “03/01/2023”, sumOver(ifelse({Jan Sales} > {Mar Cumulative Sales}, 1, 0), [ ], POST_AGG_FILTER),
max(Date) = “04/01/2023”, sumOver(ifelse({Jan Sales} > {Apr Cumulative Sales}, 1, 0), [ ], POST_AGG_FILTER),
0
) -
(max(extract(“MM”, Date)) - 1)

Hope this helps! Thank you!