Relative Difference between columns

[KK123]

Hi Folks

I have below requirement to find the difference between the last 3 columns where the data is found . tried to explain the below table. Can someone help with the workflow pls

Date

Difference in Days

Difference in Days

Difference in Days

1-Jan-21

2-Jan-21

3-Jan-21

4-Jan-21

5-Jan-21

6-Jan-21

7-Jan-21

8-Jan-21

9-Jan-21

10-Jan-21

11-Jan-21

12-Jan-21

13-Jan-21

14-Jan-21

15-Jan-21

Recent 1( Out put Required)

Prior 1 to Recent 1( Out put Required)

Prior 2 to Recent 1 ( Out put Required)

A

1

1

1

1

1

1

15Jan - 13 Jan

13Jan-7Jan

7Jan- 4 Jan

B

1

1

1

1

1

13Jan-11Jan

11Jan-8Jan

8Jan-5Jan

C

1

1

1

1

1

1

14Jan-12-Jan

12Jan-11Jan

11Jan-8Jan

D

1

1

1

1

1

15Jan - 13 Jan

13Jan-9Jan

9Jan-7Jan

[BenMoss]

Hi @KK123 the solution lies within the structure of your data, with this kind of problem it's always best to pivot your data so that you can reference values within a single field, which allows you to build a much more dynamic process.

I am struggling to understand the concepts of the solutions you want however.

The first calculation is simple to achieve, but I don't understand how column 2 works, there doesn't seem to be consistency.

In row A 'Prior 1 to Recent 1' to me should be '13Jan-10Jan' not what you have shown? Is this an error in my understanding or the example data you have used?

Ben

[BenMoss]

Based on the assumption that you have just made some mistake when sharing the data for record A, here's an example solution for you.

You'll see we can pivot our data and then assign a rank within each group, we can then use this rank to filter the different 'lookbacks' that we want.

Ben

[afv2688 · Accepted solution]

Hello @KK123 ,

I came up with this solution:

Regards

[KK123]

Thanks a lot .  I need the difference in days . Can u help with that 

[pedrodrfaria · Accepted solution]

@KK123 To complement @afv2688's answer, I believe that you just need to change the Multi Row being performed right here to do a datetime diff function. The rest should be the same.