Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a production process where unique products (ProductID) move through a series of six stages (StageID) during the course of a single day.
When a ProductID moves to a new stage, a timestamp is generated - e.g.
Timestamp | ProductID | StageID |
06:45:22 | 1 | 1 |
06:46:12 | 2 | 1 |
06:52:33 | 1 | 2 |
06:52:33 | 3 | 1 |
07:04:24 | 2 | 2 |
07:06:14 | 1 | 3 |
07:06:14 | 3 | 2 |
07:12:12 | 2 | 3 |
... | ... | ... |
I am trying to create a measure that counts all ProductIDs by current StageID when a given timestamp is selected.
My current measure is:
Count( Distinct {<Timestamp={"<=$(=Max(Timestamp))"}>} ProductID)
It gets me halfway there - it counts all unique ProductIDs up until the point in time selected. But it doesn't provide their current StageID buckets. This is where I get stuck.
The key problem is that I need the MAX(StageID) to evaluate severally within the set (i.e. for each ProductID, for only the records on/before the timestamp) before aggregating the count. Most techniques I have tried effectively apply the MAX to the whole set (thus only counting the products in the most-advanced stage bucket).
Any guidance would be much appreciated.
Apologies for not posting a sample file, the data is commercially sensitive.
For those interested, the solution I ended up devising was to combine the measure noted above with the following calculated dimension:
Aggr(FirstSortedValue({<Timestamp={"<=$(=max(Timestamp))"}>}StageID,-StageID),ProductID)
This takes the unique ProductIDs generated and buckets them into the highest StageID appearing for that ProductID before the timestamp.
For those interested, the solution I ended up devising was to combine the measure noted above with the following calculated dimension:
Aggr(FirstSortedValue({<Timestamp={"<=$(=max(Timestamp))"}>}StageID,-StageID),ProductID)
This takes the unique ProductIDs generated and buckets them into the highest StageID appearing for that ProductID before the timestamp.