Relative Difference between columns

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

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)

15Jan - 13 Jan

13Jan-7Jan

7Jan- 4 Jan

13Jan-11Jan

11Jan-8Jan

8Jan-5Jan

14Jan-12-Jan

12Jan-11Jan

11Jan-8Jan

15Jan - 13 Jan

13Jan-9Jan

9Jan-7Jan

Workflow

Accepted answers

afv2688

Hello @KK123 ,

I came up with this solution:

Regards

difference in days.yxmd

pedrodrfaria

@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.

difference in days.yxmd

All comments

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

Solution.yxmd

afv2688

Hello @KK123 ,

I came up with this solution:

Regards

difference in days.yxmd

KK123

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703