Comparing 2 dates in a pivot table

[#1]

Dear all,

I would like to compare 2 dates to understand the delays from our suppliers

the dates have the DD/MM/YY format

in the definition of the pivot table I have

Sum(Date#([requested date])- Date#([delivered date]))

This does NOT work as you can imagine

Can anyone help me: I would like the pivot table to return the sum of the delays for my suppliers (if they are delivering in advance it would be negative)

Thank you in advance

[ecolomer]

try function interval(requested date, delivered date, 'DD/MM/YYYY')

[jonathandienst]

Hi

If these two fields are already date values, then just subtract the one field from the other:

     Sum([requested date] - [delivered date])

this will return the difference in days. If they contain a time portion, then you may want

     Sum(Floor([requested date]) - Floor([delivered date]))

If they are strings, then I strongly recommend that you convert them to dates when you load them.

HTH

Jonathan

[jagan · Accepted solution]

Hi,

Try like this

Sum([requested date]- [delivered date])

If both the fields are in same table then calculate this in script itself like below

LOAD

*,

[requested date]- [delivered date] AS DaysDifference

FROM DataSource;

Now in expression you can use Sum(DaysDifference), this will have better performance.

Regards,

Jagan.

[#5]

thank you guys, all your answers are correct. I had an issue with my dates format which is now solved

[jagan]

Hi,

Please close this thread by giving Correct Answer and Helpful Answer to the posts which are helpful for you.

Regards,

Jagan.