Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Assumption based calculation

Hello to All,

I am trying to perform a complex calculation which is based on an Assumption as per company policy.

For this, we have two tables: one for receiving transactions and other for consumption transactions.

Receiving Trans:

DateItemRec QtyImported
Jan/02/2014ACB500Yes
May/02/2014ACB300No
Jan/03/2014ACB200No
Jan/03/2014ACB200Yes
Feb/08/2014ACB1000No
Mar/08/2014ACB10No
Apr/08/2014ACB1550Yes
May/03/2014ACB500Yes
Jun/03/2014ACB30No
Jul/03/2014ACB40Yes
Aug/04/2014ACB100Yes
Sep/05/2014ACB450No
Oct/02/2014ACB305Yes
Nov/02/2014ACB100No
Dec/02/2014ACB50Yes

Consumption Trans:

ItemIssued QtyDate
ACB300Jan/02/2014
ACB300May/02/2014
ACB150Jan/03/2014
ACB190Jan/03/2014
ACB800Feb/08/2014
ACB40Mar/08/2014
ACB50Apr/08/2014
ACB430May/03/2014
ACB30Jun/03/2014
ACB40Jul/03/2014
ACB80Aug/04/2014
ACB300Sep/05/2014
ACB150Oct/02/2014
ACB200Nov/02/2014
ACB130Dec/02/2014

As shown above, consumption table do not have 'Imported' Flag. But Company assume that first they will consume 'Imported' qty and after this 'un-imported' qty.

Now the requirement is that, we have to calculate month wise opening & closing stock for 'imported' and 'un-imported' Qty. If Imported qty is not available in stock then it will show zero balance.

For Example, available stock is as follow:

Month       Item       ImportedQtyStok   

-------------------------------------------------------------

Jan-2014     ACB       60

Feb-2014     ACB       0

Mar-2014    ACB         0

Apr- 2014    ACB

0 Replies