alteryx Community

# Alteryx Designer Desktop Discussions

SOLVED

## Relative Difference between columns

6 - Meteoroid

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
5 REPLIES 5
ACE Emeritus

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

ACE Emeritus

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

16 - Nebula

Hello @KK123 ,

I came up with this solution:

Regards

6 - Meteoroid

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

13 - Pulsar

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

Labels