Assumption based calculation

Fri, 06 Jun 2014 08:37:57 GMT

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:

Date	Item	Rec Qty	Imported
Jan/02/2014	ACB	500	Yes
May/02/2014	ACB	300	No
Jan/03/2014	ACB	200	No
Jan/03/2014	ACB	200	Yes
Feb/08/2014	ACB	1000	No
Mar/08/2014	ACB	10	No
Apr/08/2014	ACB	1550	Yes
May/03/2014	ACB	500	Yes
Jun/03/2014	ACB	30	No
Jul/03/2014	ACB	40	Yes
Aug/04/2014	ACB	100	Yes
Sep/05/2014	ACB	450	No
Oct/02/2014	ACB	305	Yes
Nov/02/2014	ACB	100	No
Dec/02/2014	ACB	50	Yes

Consumption Trans:

Item	Issued Qty	Date
ACB	300	Jan/02/2014
ACB	300	May/02/2014
ACB	150	Jan/03/2014
ACB	190	Jan/03/2014
ACB	800	Feb/08/2014
ACB	40	Mar/08/2014
ACB	50	Apr/08/2014
ACB	430	May/03/2014
ACB	30	Jun/03/2014
ACB	40	Jul/03/2014
ACB	80	Aug/04/2014
ACB	300	Sep/05/2014
ACB	150	Oct/02/2014
ACB	200	Nov/02/2014
ACB	130	Dec/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

topic Assumption based calculation in QlikView

Assumption based calculation