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