Filling empty values for WIP counting

[#1]

Hello,

I have a problem where I want to know my amount of works in WIP on a certain date (end of day).

I have table which consist of work order number and time stamps when ever the status of the work has changed. And there are certain statuses which are marked as WIP statuses (in this example B, C). And status of the work is valid until it is updated again.

Worknro	Timestamp	Status	WIP
123	1.7.2014	A	0
123	1.7.2014	B	1
123	6.7.2014	D	0
345	2.7.2014	A	0
345	4.7.2014	B	1
345	4.7.2014	C	1
345	6.7.2014	D	0
345	7.7.2014	E	0

So 1.7 WIP=1. As the latter status stands in the end of day.

2.7. WIP=1.

..

4.7. WIP=2 as both works are in WIP status.

..

Also the Statuses of the work don't necessary go from A>B>C>D, but can have all the combinations eg. A>C>B>C>D, A>D..  Only thing that is certain is that every work at the end has the Status which is not WIP status.

One solution in my mind is to fill the empty dates whenever the status is WIP status.

Worknro	Timestamp	Status	WIP
123	1.7.2014	A	0
123	1.7.2014	B	1
123	2.7.2014	B	1
123	3.7.2014	B	1
123	4.7.2014	B	1
123	5.7.2014	B	1
123	6.7.2014	D	0
345	2.7.2014	A	0
345	4.7.2014	B	1
345	4.7.2014	C	1
345	5.7.2014	C	1
345	6.7.2014	D	0
345	7.7.2014	E	0

But this seems inefficient, as there is a huge amount of works..

So please advice if you have better solution.

[vgutkovsky · Accepted solution]

Juha,

That's a pretty advanced scenario, but is definitely possible. I would tackle this with a date island in the data model. I'm assuming you already have a regular calendar attached to your Fact table. Clone this calendar and rename the fields so it's not linked to anything in the data model.

QUALIFY *;

DateIsland:

LOAD * RESIDENT Calendar;

UNQUALIFY *;

Your expression will need to incorporate both calendars (the regular one and this new island) and will be pretty heavy because it will need to use nested aggr statements.

count(distinct aggr(

        if(min(total <Worknro> Timestamp) <= DateIsland.Timestamp,

              only(aggr(

                    if(Timestamp=max(total <Worknro,DateIsland.Timestamp> if(Timestamp<=DateIsland.Timestamp,Timestamp)) and max(WIP)=1,

                            Worknro

                  )

                  ,Worknro,DateIsland.Timestamp,Timestamp

              ))

        )

        ,Worknro,DateIsland.Timestamp

))

The dimension of the table would be the date island date. An example is attached. If you find that this expression is too heavy, another approach could be to take the logic to the data model itself using intervalmatch and pre-aggregation. You would lose the ability to make dynamic filters (e.g. selecting a subset of work orders) but would definitely gain speed.

Regards,

Vlad

[#3]

Thanks for the example.

Almost there. I now came up with problem that I have two or more status changes in one day.

Example:

WorkNo	TimeStamp	Status	WIP	ID
W123	13.06.2014, 12:31:02.614	1	0	9453769
W123	16.06.2014, 13:39:17.661	1	0	9465562
W123	16.06.2014, 13:39:23.183	2	1	9465564
W123	16.06.2014, 13:39:46.334	K	0	9465571
W123	18.06.2014, 08:51:03.167	R	0	9480310

So in this case the work has never been in WIP stage in the end of any day. But now it shows for this WorkNo WIP status on 16.6 & 17.6. In the Load script TimeStamp is formatted to date format (DD.MM.YYYY).

In the Load script the data is loaded in "right" order. Or to say that in database the data is in timestamp order, every new status modification comes up with new timestamp (and unique ID).

So the question is how to handle this.. Should I create every second also to the DateIsland and Load the data with exact time? Or can the Load order used somehow? As the status of the work should be in the end of day the last status valid.

[vgutkovsky]

Pretty simple modification. Your date island can remain at the day level (no need to take it to the timestamp level). Small change to the chart expression to incorporate function dayend(). See updated example, attached.

Regards,

Vlad