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

[Bennet]

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.

Br

[JohnJPS · Accepted solution]

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!

[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

[JohnJPS]

Sorry, guess I saved too late.  I just had "Sum" there.

[Bennet]

thats cool,

I think I do understand the Cross Tab now and how it works. Didn't need is so fare but that solution will make a couple thinks easier in the future.

thank @JohnJPS for the fast support.

have a great start into the weekend.

Br Bennet