Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ramanm43
New Contributor

Stock count

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

PartIDSerialNoTotalQtyCreatedDate
1A1234512017-12-01
2B1234512018-01-01
3C1234512018-02-05
4D1234512018-02-20
5E1234512018-04-02

StockOut

PartIDQtyOutDate
112018-02-02
212018-03-02
312018-05-01
412018-05-25
51NULL

StockIn

PartIDSerialNoTotalQtyReceivedDate
6G1234512018-02-01
7H1234512018-02-20
8I123451

2018-03-03

9J1234512018-04-01

The output I would like:

YearMonthTotalStock
2017Nov?
2017Dec?
2018Jan?
2018Feb?
2018Mar?
2018Apr?
2018May?

Thank you in advance for any responses.

2 Replies
MVP
MVP

Re: Stock count

Why is TotalStock all question mark? Do you know what are the exact numbers you need to get?

dwforest
Valued Contributor

Re: Stock count

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.