CODEWITHSUNDEEP
dateanalyticsspotfire
V. Ang
V. Ang

Reputation: 65

Spotfire data difference: same column

I have the following table:

Id  Claim_id    Date        
4   111         10/08/2017  
5   333         27/08/2017  
2   111         07/08/2017  
3   222         08/08/2017  
1   444         03/07/2017  
7   333         02/09/2017  
6   333         28/08/2017

there are more rows (dates) associated to the same Claim_id; column "Id" is based on column "Date" (more recent dates have a greater Id).

I need to create a Calculated Column given by the date difference over claim_id, with the following output:

Id  Claim_id    Date        Days
3   111         10/08/2017  3
1   333         27/08/2017  
2   111         07/08/2017  
4   222         08/08/2017  
7   444         03/07/2017  
6   333         02/09/2017  5
5   333         28/08/2017  1

I have tried to use the code given here: Spotfire date difference using over function but it doesn't work (it produces wrong values). I think that, maybe, it's because my table is not sorted, but I can't order it because I have no access to the source database.

How can I modify that expression? Thank you!

Valentina

Upvotes: 1

Views: 1040

Answers (1)

ksp585
ksp585

Reputation: 1770

@V.Ang- One way to do this is by adding a column 'decreasing_count'.

What this does is, it counts the number of instances of ID by date. Meaning - ID with highest date would be counted first and then followed by next instance of the same ID with date lower than the previous date and so on. Advantage of this column is, your data need not be sorted for this solution to work.

Now, using this 'decreasing_count' column calculate the difference of dates.

decreasing_count column expression:

Count([Claim_id]) over (Intersect([Claim_id],AllNext([Date])))

Note: This column works in the background. You need not display it in the table

Days calculated column expression:

Days([Date] - Min([Date]) over (Intersect([Claim_id],Next([decreasing_count]))))

Final Output:

enter image description here

Hope this helps!

Upvotes: 1

Related Questions