Hi,
I require some assistance on the best way to get a total stock count by month-end for each month.
Below are two tables I have which show stock in and stock out. I am unsure as to how the SQL should be written in Qlik Sense data load editor to accurately display stock count for each month end.
Calculation of the stock at end of the month: =Previous month stock - stockout + stockin
Stock
| PartID | SerialNo | TotalQty | CreatedDate |
|---|
| 1 | A12345 | 1 | 2017-12-01 |
| 2 | B12345 | 1 | 2018-01-01 |
| 3 | C12345 | 1 | 2018-02-05 |
| 4 | D12345 | 1 | 2018-02-20 |
| 5 | E12345 | 1 | 2018-04-02 |
StockOut
| PartID | Qty | OutDate |
|---|
| 1 | 1 | 2018-02-02 |
| 2 | 1 | 2018-03-02 |
| 3 | 1 | 2018-05-01 |
| 4 | 1 | 2018-05-25 |
| 5 | 1 | NULL |
StockIn
| PartID | SerialNo | TotalQty | ReceivedDate |
|---|
| 6 | G12345 | 1 | 2018-02-01 |
| 7 | H12345 | 1 | 2018-02-20 |
| 8 | I12345 | 1 | 2018-03-03 |
| 9 | J12345 | 1 | 2018-04-01 |
The output I would like:
| Year | Month | TotalStock |
|---|
| 2017 | Nov | ? |
| 2017 | Dec | ? |
| 2018 | Jan | ? |
| 2018 | Feb | ? |
| 2018 | Mar | ? |
| 2018 | Apr | ? |
| 2018 | May | ? |
Thank you in advance for any responses.
