Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey folks!
I need to calculate the difference between two dates, which normally seems pretty easy.
But now I'm stuck because the dates are in one table field, so like this:
ID | Date | Status
1 | 01.10.2023 | X
1 | 06.11.2023 | Y
1 | 12.12.2023 | Z (done)
So now I want to find the average difference in days between status X and Z (or any other), but I'm having some trouble finding out how to do it.
Normally you would just work with the Avg function and substract the two dates, but I'm not finding the right set expression.
I tried to do something with Aggr, but didn't work out:
Avg(Aggr({$<Status= {'Status X'}>} [Date], [ID]) - Aggr({$<Status = {'Status Z'}>} [Date], [ID]))