Deriving field from two other fields in script

[FionnM]

Hi folks,

I have two fields, "Event Documented Date" (for example, 20/10/2015) and "Event Booking Date" (30/03/2015). I've created a straight table and added a calculated dimension which subtracts the latter from the former, giving the correct answer of 204 days.

I'd like to create a field in my script called "Event Documented Interval" which simply subtracts the event booking date from the event documented date. Right now it looks like this

[Event Documented Date]-[Event Booking Date] as [Event Documented Interval],

 But when I add the dimension "Event Documented Interval" to the straight table, it gives the value as -1108032. The value should never be negative, as "Event Documented Date" is never earlier than "Event Booking Date".

Any ideas what I'm doing wrong?

Thanks.

[FionnM · Accepted solution]

I figured it out. "Event Booking Date" was being loaded as a text string rather than as a numerical field. Loading it as a numerical field cleared the issue.