Set Analysis Challenge?

Sun, 30 Jun 2013 13:55:40 GMT

Hi There,

I'm finding this one really hard and would love some help with it if anyone has time.

I'm trying to Sum up Transactions that their

1. TransDate is Greather than the Last Transaction with a CostCodeSection of "Profit"

2. LESS THAN or Equal to the User Selected AsAt Date.

ServiceCallNumber is the Grouping Dimension:

If(num(TransDate)>

If(IsNull(Max({$<CostCodeSection = {"Profit"}, TransDate={"<=$(vAsAtDate)"}>} Total <ServiceCallNumber> TransDate)), 0,

Max({$<CostCodeSection = {"Profit"}, TransDate={"<=$(vAsAtDate)"}>} Total <ServiceCallNumber> TransDate))
,
Sum({$< TransDate={"<=$(vAsAtDate)"} ,jc_trans_ref-={'CORRECTION'}>} Amount))
)

I've created a little sample attached.

Thanks

MORE DETAIL BELOW: Don't want to bore you if you can understand what I mean already.

n.b. (I do not want to do this in the data load. I can think of a way but it is too inefficient so I want to use set analysis or aggrs etc. The real one works over millions of rows.)

Ok here goes with the explanation.

1. All transactions go against ServiceCallNumbers

2. On the date that a PROFIT line happens the Service Call is no longer Work in Progress (WIP)

3. A call can be reopened. All transactions after the last Profit line are now WIP again until the next Profit Line appears.

4. This is an as at report based on the date selected by the user.

Idea is as follows:

1. The User selects a Date

2. Look backwards from this date to find the Profit line. (if no profit line is found then we want all the amounts).

If a Profit line is found (whilst looking backwards) then only could amounts after that date up until the date the user has selected.

e,g,

On line 3 if the user selects the 3rd of May the WIP amount would be $6. No Profit line exists before the 3rd of may so add up all until the as at date.

e.g. Sum (Amount) WHERE TransDate is LESS THAN AsAtDate

If the user selected 6 of may that is the profit take up day. Therefore the WIP is 0.

e.g. Sum (Amount) WHERE TransDate is LESS THAN AsAtDate AND Greater THAN Last PROFIT DATE.

note everytime a profit line happens the WIP stops. If something happens after a PROFIT line that meas work started on the Call again and it is again in WIP.

ServiceCallNumber	TransDate	JobCostRowID	CostCodeSection	jc_trans_ref	Amount	WIP Amount	AsAt
55	01-May-10	100	Subcontractor		$ 1.00	$ 1.00	01-May-10
55	02-May-10	200	Subcontractor		$ 2.00	$ 3.00	02-May-10
55	03-May-10	300	Subcontractor		$ 3.00	$ 6.00	03-May-10
55	06-May-10	400	Income		$ 4.00	$ -	06-May-10
55	06-May-10	500	Income		$ 5.00	$ -	06-May-10
55	06-May-10	600	Profit		$ 6.00	$ -	06-May-10
55	07-May-10	700	Subcontractor		$ 7.00	$ 7.00	07-May-10
55	08-May-10	800	Profit		$ 8.00	$ -	08-May-10
55	09-May-10	900	Subcontractor		$ 9.00	$ 9.00	09-May-10
55	10-May-10	1000	Income		$ 10.00	$ 19.00	10-May-10
55	11-May-10	1100	Income	CORRECTION	$ 11.00	$ 19.00	11-May-10

topic Set Analysis Challenge? in QlikView

Set Analysis Challenge?