topic Re: Make Date in QlikView

Make Date

vanderson009 — Thu, 03 Apr 2014 09:56:19 GMT

Hi community,

Suppoes i have to create date for any previous year by using makedate function.

and using that date i have to genarate dates till today date.

How should i achive this task.

- Regards,

Villyee Anderson

Re: Make Date

datanibbler — Thu, 03 Apr 2014 10:01:04 GMT

Hi,

try sth like

>>> makedate(year(yearstart(today(), -1)), 1, 1 <<< (for Jan 1 2013 in this example)

Re: Make Date

MK_QSL — Thu, 03 Apr 2014 10:07:12 GMT

MinMaxDate:

Load

YearStart(MakeDate(2010)) as MinDate, //Change 2010 to any year...

Today() as MaxDate

AutoGenerate 1;

Let vMinDate = NUM(Peek('MinDate',0,'MinMaxDate'));

Let vMaxDate = NUM(Peek('MaxDate',0,'MinMaxDate'));

Let vNoOfDays = vMaxDate - vMinDate + 1 ;

Drop Table MinMaxDate;

DateField:

Load

Date($(vMinDate) + RowNo() - 1) as TempDate

AutoGenerate $(vNoOfDays);

Re: Make Date

Thu, 03 Apr 2014 10:08:14 GMT

Hi,

Try this

Let vMinDateNum =Num(Date(Makedate(2013,1,1),'DD/MM/YYYY'));
Let vMaxDateNum =Num(Date(Today(),'DD/MM/YYYY'));
Let vDiff=vMaxDateNum-vMinDateNum;

Calender_Temp:
Load
Date($(vMinDateNum)+RowNo()-1) AS TempDate
AutoGenerate ($(vDiff)+1);

Re: Make Date

crusader_ — Thu, 03 Apr 2014 10:08:30 GMT

Hi Villyee,

Try to use this script:

LET varMinDate = num(date('01.01.2010')); //For example

LET varMaxDate = num(today()); //For example

[tmpCalendar]:

LOAD

DATE($(varMinDate) + RowNo() - 1) AS [Date]

AUTOGENERATE $(varMaxDate) - $(varMinDate) + 1;

[Calendar]:

LOAD

[Date],

YEAR([Date]) AS [Year],

CEIL(MONTH([Date])/6) AS [Half-year],

CEIL(MONTH([Date])/3) AS [Quarter],

MONTH([Date]) AS [Month],

DUAL('M ' & YEAR([Date]) & '-' & NUM(MONTH([Date]), '00'), YEAR([Date]) * 100 + Month([Date])) AS [Year, month],

DAY([Date]) AS [Day of Month],

WeekYear([Date]) AS [Year of Week],

WEEK([Date]) AS [Week],

WeekDay([Date]) AS [Day of Week],

RESIDENT tmpCalendar;

DROP TABLE tmpCalendar;

It generates Calendar from date1 to date2.

Kind regards,

Andrei

Re: Make Date

its_anandrjs — Thu, 03 Apr 2014 10:11:07 GMT

Create your date bu this code

Let varMinDate = Num(Makedate(2006,1,1));

Let varMaxDate = Num(Makedate(Year(today()),Month(today()),Day(today())));

Datefield:

LOAD date($(varMinDate)+IterNo()-1) AS Datefield

AUTOGENERATE (1)

WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);

Re: Make Date

arjunkrishnan — Thu, 03 Apr 2014 10:13:23 GMT

Hi Frd Use This One Very Useful For U

Let vDateMin = Floor(Num(MakeDate(2013,1,1)));

Let vDateMax = Today();

Regrads ArjunKrish

Re: Make Date

Thu, 03 Apr 2014 10:18:22 GMT

Range:

LOAD

Min([Order Date]) as MinDate,

Max([Order Date]) as MaxDate

RESIDENT Orders;

LET vMinDate = Num(Peek('MinDate',0,'Range'));

LET vMaxDate = Num(Peek('MaxDate',0,'Range'));

LET vToday = $(vMaxDate);

TempCal:

LOAD

date($(vMinDate) + RowNo() -1) as TempDate

Autogenerate

$(vMaxDate) - $(vMinDate) + 1;

DROP TABLE MinMax;

Calender:

LOAD

TempDate as Date,

RESIDENT TempCal

ORDER BY TempDate ASC;

DROP TABLE TempCal;