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
