topic Comparing 2 dates in a pivot table in QlikView

Comparing 2 dates in a pivot table

Thu, 21 Aug 2014 06:59:35 GMT

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

Re: Comparing 2 dates in a pivot table

ecolomer — Thu, 21 Aug 2014 07:05:22 GMT

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

Re: Comparing 2 dates in a pivot table

jonathandienst — Thu, 21 Aug 2014 07:11:05 GMT

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

Re: Comparing 2 dates in a pivot table

jagan — Thu, 21 Aug 2014 08:49:14 GMT

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.

Re: Comparing 2 dates in a pivot table

Fri, 22 Aug 2014 00:38:40 GMT

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

Re: Comparing 2 dates in a pivot table

jagan — Fri, 22 Aug 2014 01:34:42 GMT

Hi,

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

Regards,

Jagan.