Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Everyone,
I'm struggling trying to visualize warehouse stock levels with Year-Month filters.
The one below is the data table of the stock in/out movements for two items (assuming no stock in the warehouse before 17/11/2022)
ITEM | Date | Stock In/Out |
1 | 17/11/2022 | 2 |
1 | 28/11/2022 | 1 |
2 | 14/11/2022 | 7 |
1 | 16/12/2022 | -1 |
2 | 14/12/2022 | -1 |
1 | 03/01/2023 | 2 |
1 | 15/01/2023 | 3 |
2 | 27/01/2023 | -2 |
1 | 15/02/2023 | 9 |
2 | 17/02/2023 | -2 |
No problems visualizing stock levels in a bar chart with
RangeSum(Above(Sum(Stock In/Out), 0,RowNo()))
Year-Month | Stock In/Out No Filters | Stock Level No Filters |
2022-nov | 10 | 10 |
2022-dic | -2 | 8 |
2023-gen | 3 | 11 |
2023-feb | 7 | 18 |
but only if I don't apply filters on the bars dimension (year-month)
How can I visualize stock levels of just some months without losing the information about stock movements of the other months?
I'd like to filter, for example, from dec 2022 to feb 2023, and obtain stock levels for item 1 (data below)
Year-Month | Stock In/Out Item 1 Dic2022-Feb2023 | Stock Level Item 1 Dic2022-Feb2023 |
2022-dic | -1 | 2 |
2023-gen | 5 | 7 |
2023-feb | 9 | 16 |
Right now this is what I get (november's data not used in the cumulative sum)
Year-Month | Stock In/Out Item 1 Dic2022-Feb2023 | Stock Level Item 1 Dic2022-Feb2023 |
2022-dic | -1 | -1 |
2023-gen | 5 | 4 |
2023-feb | 9 | 13 |
Let me know if you need any additional information about and thank you for your help!
In order to visualize stock levels with Year-Month filters without losing the information about stock movements of the other months, you can create a variable that holds the cumulative sum of stock levels up to the current month, and use that variable to calculate the stock levels for the selected months. Here's an example of how you can do this:
Create a variable 'Cumulative Sum' with the following expression: =RangeSum(Above(Sum({$<[Year-Month]={"<=$(=Max([Year-Month]))"}>} Stock In/Out), 0, RowNo()))
Create another variable 'Stock Level' with the following expression: =Sum({$<[Year-Month]={">=$(=Min([Year-Month])), <=$(=Max([Year-Month]))"}>} Stock In/Out) + $(Cumulative Sum)
Create a chart, where on the X axis you put the Year-Month, and on the Y axis the Stock Level variable.
The variable 'Cumulative Sum' holds the cumulative sum of the stock levels up to the current month, including the months outside of the selected range. The variable 'Stock Level' calculates the stock levels for the selected months by adding the stock movements within the selected range to the cumulative sum up to the current month.
By using this approach, the stock level of the selected months will be calculated based on the stock level of all the previous months, even if they are not included in the selected range. This way, you will not lose the information about stock movements of the other months.