topic Re: Calculated field will not return correct value for difference between dates. in QlikView

Calculated field will not return correct value for difference between dates.

ecrobles18 — Wed, 22 Jun 2016 21:12:43 GMT

Hello All,

I'm having an issue with a calculated date field where I need to return the difference between two dates, a Release Date and Delivery Date. I have scripted it and I am getting a variety of results with the majority not being correct. I have designed it to return the actual value when it is a positive value and I wan it to subtract the delivery date from today if the value is negative. In the case that the number value is 0 I want it to return 0. How should I format the date while pulling it in and should I calculate it via set analysis on the presentation layer? I have listed the script, if anyone could provide some advice on how I can address this it would be truly appreciated. Thank you in advance.

if(num(Date.4.Release-Date.2.Delivery)<=0, num(Today()-Date.2.Delivery,'#,##0'), num(Date.4.Release-Date.2.Delivery,'#,##0')) as Layover

Re: Calculated field will not return correct value for difference between dates.

swuehl — Wed, 22 Jun 2016 21:47:23 GMT

It's important to get the date fields loaded with a numeric representation, see

Get the Dates Right

Why don’t my dates work?

Data Types in QlikView

Re: Calculated field will not return correct value for difference between dates.

johnw — Wed, 22 Jun 2016 23:52:57 GMT

What swuehl‌ said and linked to - dates should be date fields, typically read in using the date#() function. And I would keep the calculation in script rather than the presentation layer as long as these two dates are on the same table. And that's my general philosophy - keep the complication in script except for aggregations. Do all your aggregating in the presentation layer.

Re: Calculated field will not return correct value for difference between dates.

sunny_talwar — Thu, 23 Jun 2016 04:01:19 GMT

Not sure which part of the if statement is giving you incorrect answers (true or false or both), but you mentioned you wanted to see 0 when release - delivery = 0. I think in your today's script, if the difference is 0, it will look for difference between today and delivery. You might want to change it to this:

If(Date.4.Release-Date.2.Delivery < 0, Today() - Date.2.Delivery, Date.4.Release - Date.2.Delivery) as Layover

Notice that I removed that equal (=) sign from the if statement's condition. I also removed the use of Num function as difference of two dates should be a number (i.e. if they are true date fields with dual orientation).