Get Item Status at particular time

[wanyunyang]

I have two tables:

Item table with: Item, OrderDate

ItemStatus table, which has item status change information :

Item, StatusChangeDate1, OldStatus1, NewStatus1

(If status has changed multiple times, may also have: Item, StatusChangeDate2, OldStatus2, NewStatus2)

I'm trying to link these two tables together. By selecting an OrderDate, I want it give me the item status at that time.

How should I build the model? Any advice helps! Thank you in advance!

[kingsley101]

Hi Wanyun.

Are you saying that a new date and status field are created everytime a status is changed?

[wanyunyang]

Hi Kingsley, yes you are right.

[kingsley101]

I think to build this, you should consider having a field called "Item", another field called "Modified date" and another field called "Status"... This would be a temporary table that you load to make your final product...

If you aren't needing the historical data in the model, then you can do this:

Left join(status)

Load Item,

Max([Modified date]) as [Modified date]

Resident status

Group by Item;

That way you will only have the latest status for each item

But if you are wanting to keep the historical data in the model, then you don't need the bold section.

Thanks

Kingsley

[wanyunyang]

Hi Kingsley,

Yes I already have those in my ItemStatus table. The only thing I can't achieve now is by selecting an order date, how can I be able to see the item status at that time.

[kingsley101]

Hi Wanyun.

So this is what you'll need to do:

Load your order table (the date field is called OrderDate)

Load your Item Status table (All Dates must be in one field called Date, All Status in 1 field called Status)

(These 2 tables need to associate on the field Item)

Create a straight table with the Dimensions:

Item,

OrderDate,

Status

=IF(Date=Date(AGGR(Max(IF(Date<=OrderDate,Date)), Item, OrderDate),'DD/MM/YYYY'),Date) //Make sure this calculated dimension is suppressed when null

You can hide the calculated dimension (or any other dims) on the presentation tab of the chart properties.

Thanks