0 Replies Latest reply: Jun 30, 2013 9:55 AM by Luke Chatterton

Set Analysis Challenge?

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

ps

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