Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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
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;
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