topic Re: Need to compare two dates columns in App Development

Need to compare two dates columns

Anonymous — Tue, 17 Oct 2017 13:06:43 GMT

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

Re: Need to compare two dates columns

felipedl — Tue, 17 Oct 2017 13:54:16 GMT

Hi Ravi,

During the load script, use the Interval function.

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

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.

Re: Need to compare two dates columns

Anonymous — Tue, 17 Oct 2017 16:15:02 GMT

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

Re: Need to compare two dates columns

felipedl — Tue, 17 Oct 2017 16:23:50 GMT

Hi Ravi,

Try something like this:

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;

Re: Need to compare two dates columns

Anonymous — Tue, 17 Oct 2017 16:40:56 GMT

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