Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have the following situation:
WorkOrder | Pos | Date | FINAL |
---|---|---|---|
4711 | 1 | - | 23.03.2017 |
4711 | 2 | 23.03.2017 | 23.03.2017 |
4711 | 3 | - | 25.03.2017 |
4711 | 4 | 25.03.2017 | 25.03.2017 |
4712 | 1 | 12.02.2017 | 12.02.2017 |
4712 | 2 | - | 12.02.2017 |
4712 | 3 | 17.02.2017 | 17.02.2017 |
The table consists of the fields"WorkOrder", "Pos" and "Date". The desired result is the column "FINAL"
If the date in the column "Date" is null, then I want to set the next available date.
Please notice that I have to calculate this with every WorkOrder.
This is an easy example of the data, the real data model is quite difficult, so I can't do the calculation in the script (e.g. with peek)
So please only suggestions with an expression in a diagram.
So long and many thanks in advance,
And if you could have more than one Date value missing in WorkOrder / Pos sequence, but your dates are only ascending, you can also combine Below() with a RangeXXX function:
WorkOrder | Pos | Date | Alt(Date, Date(Rangemin(below(Date,1,NoOfRows())))) | Alt(Date,Below(Date)) |
---|---|---|---|---|
4711 | 1 | - | 23.03.2017 | 23.03.2017 |
4711 | 2 | 23.03.2017 | 23.03.2017 | 23.03.2017 |
4711 | 3 | - | 25.03.2017 | 25.03.2017 |
4711 | 4 | 25.03.2017 | 25.03.2017 | 25.03.2017 |
4711 | 6 | 31.03.2017 | ||
4711 | 7 | 31.03.2017 | 31.03.2017 | |
4711 | 8 | 31.03.2017 | 31.03.2017 | 31.03.2017 |
4712 | 1 | 12.02.2017 | 12.02.2017 | 12.02.2017 |
4712 | 2 | - | 17.02.2017 | 17.02.2017 |
4712 | 3 | 17.02.2017 | 17.02.2017 | 17.02.2017 |
Note that chart inter record functions consider so called column segments, so dimensions and their sort order are important here.
You could look into chart inter record functions like Below():
WorkOrder | Pos | Date | Alt(Date, below(Date)) |
---|---|---|---|
4711 | 1 | - | 23.03.2017 |
4711 | 2 | 23.03.2017 | 23.03.2017 |
4711 | 3 | - | 25.03.2017 |
4711 | 4 | 25.03.2017 | 25.03.2017 |
4712 | 1 | 12.02.2017 | 12.02.2017 |
4712 | 2 | - | 17.02.2017 |
4712 | 3 | 17.02.2017 | 17.02.2017 |
And if you could have more than one Date value missing in WorkOrder / Pos sequence, but your dates are only ascending, you can also combine Below() with a RangeXXX function:
WorkOrder | Pos | Date | Alt(Date, Date(Rangemin(below(Date,1,NoOfRows())))) | Alt(Date,Below(Date)) |
---|---|---|---|---|
4711 | 1 | - | 23.03.2017 | 23.03.2017 |
4711 | 2 | 23.03.2017 | 23.03.2017 | 23.03.2017 |
4711 | 3 | - | 25.03.2017 | 25.03.2017 |
4711 | 4 | 25.03.2017 | 25.03.2017 | 25.03.2017 |
4711 | 6 | 31.03.2017 | ||
4711 | 7 | 31.03.2017 | 31.03.2017 | |
4711 | 8 | 31.03.2017 | 31.03.2017 | 31.03.2017 |
4712 | 1 | 12.02.2017 | 12.02.2017 | 12.02.2017 |
4712 | 2 | - | 17.02.2017 | 17.02.2017 |
4712 | 3 | 17.02.2017 | 17.02.2017 | 17.02.2017 |
Note that chart inter record functions consider so called column segments, so dimensions and their sort order are important here.
Hi Stefan,
many thanks, worked out great. I Wasn't thinking of the "alt"-function.