difference in dates that are in the same column by dimension

[1600eads]

Hi all, previously asked a similar question but the solution didn't quite get me there. 

I am trying to find the difference in two dates that are coming from a single column based on a dimension value. 

I found a post regarding a similar issue but was unable to yield any results, even at an aggregated level. 

I have tried the expression: =interval( only({<STATUS= {'COMPLETE'}>} DATES) - only({<STATUS= {'PENDING'}>} DATES))

Can anyone point me in the right direction on how to conduct a basic arithmetic of finding the difference between two dates that is coming from the same column? Thank you all.

[Anil_Babu_Samineni]

You have always only two records for each ID with PENDING and COMPLETE? 

[1600eads]

The value can be null if the process hasn't been COMPLETE yet so technically there is a 3rd which will be null(). But if an ID has both COMPELTE and PENDING, it'll tell me that this clients' process was completed which in that case will always have those two values. 

[Anil_Babu_Samineni]

Please share data in tabular or excel instead of Image also add as many combinations as possible that you have met the real data. 

[1600eads]

Due to the nature of the data sensitivity, I attached a few more rows on the sample data. You will notice in some instances, because it has not been completed, there will be no date or a sales person tied to that row.

Much appreciated for your assistance.

[Anil_Babu_Samineni · Accepted solution]

Perhaps this?

Aggr(Interval(Only({<STATUS={'COMPLETE'}>} UPDATED_DATES)-Only({<STATUS={'PENDING'}>} UPDATED_DATES), 'DD'), ID)

[1600eads]

Thank you! This worked like a charm. I am seeing what went wrong. I needed to aggregate it by the ID but its making sense now. Thanks again and Happy New Year.