6 Replies Latest reply on Apr 10, 2018 9:38 AM by Deepak Rai

# Calculate difference of Date in the same column

Dears,

I'm struggling with a simple calculation between dates on the same column,

My set of data looks like this:

 PO# Item type Date 4500920239 1 GR 1/12/2018 4500920239 2 GR 1/12/2018 4500920239 1 IN 1/14/2018 4500920288 1 GR 1/14/2018 4500920288 1 IN 1/9/2018

and I would like to obtain a report By PO# & "Item" (unique combination)  which will display the date difference  between Type "IN" - TYPE "GR"

The output of the report should be

 PO# Item date Diff 4500920239 1 2 4500920239 2 NULL 4500920288 1 -5

4500920239 --> 1          1/14 - 1/12

4500920239 --> NULL  Because there is GR But not IN

4500920288 --> -5        1/9 - 1/14

Thanks a lot,

Cristian

• ###### 1. Re: Calculate difference of Date in the same column

Here it is:

Thanks

Deepak

If it Helps, Pl mark it Helpful and CORRECT to Close Thread.

• ###### 2. Re: Calculate difference of Date in the same column

Hi Deepak, Thanks a lot, this answer the question, can i ask something else? if you want to count the number of negatives differences by PO how would you aggregate?

I attempted with your calculation but Im ending on aggregation of aggregation. Thanks a lot!

In the above example I would like to get

PO#              Occurrences

4500920288  1

• ###### 3. Re: Calculate difference of Date in the same column

Hi Deepak, Thanks a lot, this answer the question, can i ask something else? if you want to count the number of negatives differences by PO how would you aggregate?

I attempted with your calculation but Im ending on aggregation of aggregation. Thanks a lot!

In the above example I would like to get

PO#              Occurrences

4500920288  1

• ###### 4. Re: Calculate difference of Date in the same column

Thanks

Deepak

• ###### 5. Re: Calculate difference of Date in the same column

Hi Deepak, it seems you posted a solution and it’s been deleted. Can you please post it again sometime I can take a look and close the thread ?

Thanks a lot for your great help

Sent from my iPhone

• ###### 6. Re: Calculate difference of Date in the same column

Hi Cristian,

Here we are dealing with aggregated data, so you need a Calc like this.

Thanks

Deepak

If it Helps, pl mark it Helpful and CORRECT to close Thread