Need to compare two dates columns

[Anonymous]

Hello Guys , I have two columns of Actual Date and Forecast dates . I need to compare these two columns and need to find the difference between these two dates . Could you please help me how to do this . How do I show difference ?

Actual date	Forecast Dates
12/12/2016	01/09/2016

I need one more column to show that whats the difference by how many (days, months , years)  between actual and forecast

Thanks In Advance,

ravi

[felipedl]

Hi Ravi,

During the load script, use the Interval function.

As an example, I did something like the bellow script:

x:

load * Inline

[

Actual date, Forecast Dates

12/12/2016, 01/09/2015

];

date:

Load

*,

Interval([Actual date]-[Forecast Dates],'d') as [Difference Days],

floor(Interval([Actual date]-[Forecast Dates],'d')/30) as [Difference Months],

floor(Interval([Actual date]-[Forecast Dates],'d')/30/12) as [Difference Years]

Resident x;

drop table x;

For your data, it would give the following

Felipe.

[Anonymous]

Thanks a lot for the detailed expression !

My Another question is as follows:-

I have a Date column and I want to check if there are any null values in the Date column . And if there are null values in the date column then I want to put today's date and subtract it with forecast dates .

How should this be done .

Thanks,

ravi shinge

[felipedl]

Hi Ravi,

Try something like this:

x:

load * Inline

[

Actual date, Forecast Dates

12/12/2016, 01/09/2015

,01/05/2014

];

date:

Load

*,

Interval([Actual date]-[Forecast Dates],'d') as [Difference Days],

floor(Interval([Actual date]-[Forecast Dates],'d')/30) as [Difference Months],

floor(Interval([Actual date]-[Forecast Dates],'d')/30/12) as [Difference Years];

Load

// Gets the nulls with today date

if(len([Actual date])>0,[Actual date],date(Today(),'MM/DD/YYYY')) as [Actual date],

[Forecast Dates]

Resident x;

drop table x;

[Anonymous · Accepted solution]

Thanks again for instant reply !

I am writing this as an expression as I am creating dimension or so called column to represent these date differences .

So now if i write this as an expression to create a dimension  .

So now I wrote IF condition  but how to write then condition  :-

For instance I write below code in the expression :-  

if(len([Actual date])>0,[Actual date],date(Today(),'MM/DD/YYYY'))     ------- upto here I am fine as I am checking IF condition  but after this Again I want to Subtract    Today's date with Forecast dates 

so how should i write here in the expression .How do I connect IF condition and below mentioned code

Ex:- Interval [Today date - [Forecast date ]] ,'d')

Thanks,

Ravi