Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Why is TotalStock all question mark? Do you know what are the exact numbers you need to get?
Add a master calendar (Master Calendar Generation Script) to provide easy access to Year and Month of each date...
then your total would be Sum(TotalQty), Qlik will figure it out for you for each row of your Year / Month in the table.