Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

ecrobles18
New Contributor III

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

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

3 Replies
MVP
MVP

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

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

MVP
MVP

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

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.

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

Community Browser