topic Re: Date in QV in QlikView

Date in QV

Thu, 10 Apr 2014 05:56:09 GMT

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

Re: Date in QV

its_anandrjs — Thu, 10 Apr 2014 06:02:04 GMT

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

Re: Date in QV

Anonymous — Thu, 10 Apr 2014 06:02:58 GMT

hi kitty,

use the below expression:

=date(addmonths(date,4,1),'MMM-YYYY')

regards,

vaibs

Re: Date in QV

maxgro — Thu, 10 Apr 2014 06:04:00 GMT

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

Re: Date in QV

sujeetsingh — Thu, 10 Apr 2014 06:04:11 GMT

Just create the new column as

-----In UI as Date1-Date2 in months

-----In Script as Month(Date1)- 4

Re: Date in QV

its_anandrjs — Thu, 10 Apr 2014 06:04:34 GMT

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

Re: Date in QV

Thu, 10 Apr 2014 06:51:16 GMT

Thanks guys, this is very helpful!

Re: Date in QV

Thu, 10 Apr 2014 08:15:56 GMT

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?