Data Model Question

[#1]

HI guys,

Here is how it my data set looks like.

Main:

ID      Amt           Qty                Date                        ShipDate                    Type

1        101           14              09/10/2011                                              Sales Order

2        56             4               10/11/2011                   11/11/2011           Invoice

3        45             10              11/01/2012                  12/12/2012          Invoice

Link:

ID           LinkedID          

1              2                      

1              3                        

2               1                         

3               1                       

1.      Sales Order (ID :1) has quantity 14.

2.      This sales order is partially fulfilled(Quantity: 4) by an invoice (ID: 2) on 10/11/2011.

            The ShipDate on this invoice is 11/11/2011.

3. The remaining quantities on the sales order is fulfilled by an Invoice (ID: 3) on 11/01/2012.

            The ShipDate this invoice is 12/12/2012.

4. The Link table maintains the double linking between transactions.

5. Sales Order (ID: 1) is linked with 2 invoices (ID: 2 and 3)

6. Invoices (ID: 2 and 3) are linked with one Sales Order (ID : 1)

7. The chart should show the latest shiporder of the Sales Order (ID : 1) which is 12/12/2012.

Type                ID            OrderDate          ShipDate                   

SalesOrder     1               09/10/2011         12/12/2011      

I can change data model if required.

Thanks,

Saurabh

[#2]

If I understand you right.

you don't need to do any modelling as such. The two tables that you have will be linked by ID.

When creating your chart, you should concentrate on how you code your expression.

Example ,  =if (Date=linkedDate, Max(Shipdate))  (Psudo)

What do you think? let me know

[#3]

Sorry but I did not get it.

my chart should look like this

Type                ID            OrderDate          ShipDate

SalesOrder     1               09/10/2011         02/02/2011

SalesOrder     4               05/11/2013          05/01/2014

Can you please help me understand that how your suggested way will give this chart?

[#4]

Ok, I see, you are wanting to go through all the linked records and get the latest.

for example: ID 1 is linked to IDs 2 and 3 and out of those you want to get the latest ship date.

Am I correct?

[#5]

Yes