Calculation based on Assumptions

Fri, 06 Jun 2014 08:46:39 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

Excisable

Jan/02/2014

ACB

500

Yes

May/02/2014

ACB

300

Jan/03/2014

ACB

200

Jan/03/2014

ACB

200

Yes

Feb/08/2014

ACB

1000

Mar/08/2014

ACB

Apr/08/2014

ACB

1550

Yes

May/03/2014

ACB

500

Yes

Jun/03/2014

ACB

Jul/03/2014

ACB

Yes

Aug/04/2014

ACB

100

Yes

Sep/05/2014

ACB

450

Oct/02/2014

ACB

305

Yes

Nov/02/2014

ACB

100

Dec/02/2014

ACB

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 'Excisable' qty and after this 'un-excisable' qty.

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

For Example, available Excisable stock should be as follow:

Month Item ExcisableStok Qty

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

Jan-2014 ACB 60

Feb-2014 ACB 0

Mar-2014 ACB 0

Apr- 2014 ACB 1500

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

Please provide your suggestion to accomplish this.

Thanks in Advance

topic Calculation based on Assumptions in QlikView

Calculation based on Assumptions