IF expression issue - search other ID and give out specific information in different colum

Hello,

I am working again on something that is a little to tricky for me and seems to be the right thing for a good start into the weekend.

This time I need some help with a formula.

What I like to achieve:

ID	Status	Time	needed time total
1	new	01.01.
1	progress	02.01.
1	solved	03.01.	2 days
2	new	02.01.
2	solved	08.01.	6 days

If the status for an ID is solved then it should search for the same ID with status "new" and calculate the total time that was needed to solve the ID.

Any ideas? Maybe to steps calculation? first provide the right time and in the next step calculate the TimeDiff? Major problem here is that I don't know how to get the time out of the line with the same ID (status) "new" in a new field.

thanks for reading it.

Date Time

Preparation

Expression

Accepted answers

JohnJPS

Hi @Bennet,

I have a couple approaches which may or may not work according to your needs. One uses a Cross Tab and the other uses a MultiRow formula.

Cross Tab: group on ID with Header being status and Value being time, so that I have ID, New, Progress, and Solved as my columns. Then a simple formula does the calculation.

MultiRow Formula: this uses a simple formula, to carry the "new" time along until it hits "solved" and then does the calculation.

Both approaches are in the attached v11.0 formula. Also, I'm not sure how you're representing times.. I just converted to a double and did a simple subtraction. However you may make the calculation however you desire.

Hope that helps!

calcTimes.yxmd

All comments

JohnJPS

Hi @Bennet,

I have a couple approaches which may or may not work according to your needs. One uses a Cross Tab and the other uses a MultiRow formula.

Cross Tab: group on ID with Header being status and Value being time, so that I have ID, New, Progress, and Solved as my columns. Then a simple formula does the calculation.

MultiRow Formula: this uses a simple formula, to carry the "new" time along until it hits "solved" and then does the calculation.

Hope that helps!

calcTimes.yxmd

Bennet

Hi @JohnJPS,

thank you for the fast response. It seems that there is an error: "Cross Tab (2): At least one CrossTab Method must be specified (Not counting Total Row)".

which one did you use for the calculation?

Br Bennet

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703