Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I have a question as follows. Original I have the date variable, how to get 4 months later for each record as the new variable date1?
Anyone could help? Thanks!
Original table:
ID | date | score |
---|---|---|
1 | 12/2009 | 80 |
2 | 06/2011 | 50 |
Final table
ID | date1 | date | score |
---|---|---|---|
1 | 04/2010 | 12/2009 | 80 |
2 | 10/2011 | 06/2011 | 50 |
New update let see the date function
Use Addmonths() function for date field
Date(AddMonths(Date#(date,'MM/YYYY'),4),'MM/YYYY') as Newdatefield
Load your table like
LOAD Date#(date,'MM/YYYY') AS date,ID,score,Date(AddMonths(Date#(date,'MM/YYYY'),4),'MM/YYYY') as date1;
LOAD * Inline
[
ID, date, score
1, 12/2009, 80
2, 06/2011, 50
];
See the snap shot
New update let see the date function
Use Addmonths() function for date field
Date(AddMonths(Date#(date,'MM/YYYY'),4),'MM/YYYY') as Newdatefield
Load your table like
LOAD Date#(date,'MM/YYYY') AS date,ID,score,Date(AddMonths(Date#(date,'MM/YYYY'),4),'MM/YYYY') as date1;
LOAD * Inline
[
ID, date, score
1, 12/2009, 80
2, 06/2011, 50
];
See the snap shot
hi kitty,
use the below expression:
=date(addmonths(date,4,1),'MMM-YYYY')
regards,
vaibs
try this for a constant '12/2009'
=date(addmonths(date#('12/2009', 'MM/YYYY'),4), 'MM/YYYY')
and for the field (date, in bold)
date(addmonths(date#(date, 'MM/YYYY'),4), 'MM/YYYY')
useful help for dates here
http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/19/why-don-t-my-dates-work
Just create the new column as
-----In UI as Date1-Date2 in months
-----In Script as Month(Date1)- 4
New update
For date field write like
AddMonths(Date#(date,'MM/YYYY'),4)
In script level you can do like
Dimension1:-ID
Dimension2:- date
Calculated Dimension3 for date1:- =Date(AddMonths(Date#(date,'MM/YYYY'),4),'MM/YYYY')
Expression:- Sum(score)
Regards
Thanks guys, this is very helpful!
Additional question, if my original date is 12/31/2008 12:00:00 AM, then how to convert this get the final 04/2010 as date1?