Calculate open orders before period

peschu123 — Wed, 04 Dec 2013 11:39:01 GMT

Hi,

I need to count the open orders before each period and the finished/started orders in each period.

Finally I want a straight table like this(Financial Year(FY) is chosen via listbox):

Dimension: FYFP

FYFP	Open	Start	Finish
FY13/14FP01	500	1400	1450
FY13/14FP02	9	44	130
FY13/14FP03	15	65	78
FY13/14FP04	22	62	112
FY13/14FP05	19	117	108
FY13/14FP06	35	110	108
FY13/14FP07	84	38	86
FY13/14FP08	200	184	101
FY13/14FP09	158	11	7
FY13/14FP10	131	0	0
FY13/14FP11	303	0	0
FY13/14FP12	236	0	0

Open Orders:

I have a "start" and a "delivery date" for each order.

To calculate the open orders in the system before each period I need to check the following

pseudocode: Count(IF("Startdate" < "FYFP" AND ([Delivery Date] > "FYFP" OR len([Delivery Date])>0), OrderID))

What I don't get is how to check the dates against the FYFP. I use a linktable for the dates(see the screenshot).In this case I don't use Dual fields for the dates.

For example the orders started/finished in a period I simply count with:

=Count({<Datetype={POC}>}OrderID) - POC = Start Date

=Count({<Datetype={DDA}>}OrderID) - DDA = Delivery Date

Thank you in advance and best regards,

Peter

topic Calculate open orders before period in QlikView

Calculate open orders before period