topic Generating Dates in QlikView

Generating Dates

Tue, 02 Aug 2011 04:13:38 GMT

Guys,

I need to generate Dates.A list Box has years in it.

Now based on the year selection I need to generate dates.Something like this..

If I select 2009 then the dates I generate will be like

1st Jan 2009,8 Jan 2009,15 Jan 2009,........31st Dec 2009.

These dates should be like dimensions in a bubble graph.

No idea how to do do this.Please let me know if anybody has an idea.

Regards

Navin.G

Generating Dates

rahulgupta — Tue, 02 Aug 2011 05:04:17 GMT

Hey Hi,

you can use the following code for the autogenerate dates at the back-end and then connect it via Year to tha Data Model:

LET MinYear = year(today())-2;

LET MaxYear = Year(today());

LET MonthMin = 1;

LET MonthMax = 12;

LET DayMin = 1;

LET DayMax =30;

for Y = $(MinYear) to $(MaxYear)

for M = $(MonthMin) to $(MonthMax)

for D = $(DayMin) to $(DayMax)

Calender:

LOAD

$(Y) AS Year,

$(M) AS Month;

$(D) AS Day,

makedate($(Y),$(M),$(D)) as Date

AUTOGENERATE(1) ;

NEXT D

NEXT M

NEXT Y

Hope this helps....

Regards

Generating Dates

rwunderlich — Tue, 02 Aug 2011 05:08:01 GMT

Please describe more about what your application is. You can generate dates a week apart -- like the WeekStart date -- in your master calendar and use thosevalues as a dimension.

-Rob

Re: Generating Dates

Tue, 02 Aug 2011 05:09:13 GMT

Rahul,

Thanks for your reply.Have a small doubt.

I need to generate dates having a seven day gap from 1st Jan.

Something like 1st Jan 2009,7th Jan 2009.

Regards

Navin

Generating Dates

rahulgupta — Tue, 02 Aug 2011 05:33:49 GMT

Hi Navin,

For that you may use:

LET MinYear = year(today())-2;

LET MaxYear = Year(today());

LET MonthMin = 1;

LET MonthMax = 12;

LET DayMin = 1;

LET DayMax =30;

for Y = $(MinYear) to $(MaxYear)

for M = $(MonthMin) to $(MonthMax)

for D = $(DayMin) to $(DayMax)

Calender:

LOAD

$(Y) AS Year,

$(M) AS Month;

$(D) AS Day,

makedate($(Y),$(M),$(D)) as Date

AUTOGENERATE(1) ;

D=$(D)+5;

NEXT D

NEXT M

NEXT Y

Hope this Helps...

Regards

Re: Generating Dates

Tue, 02 Aug 2011 05:35:49 GMT

Hi Rob and Rahul,

Below is the excel sheet attched which has four columns--Name Location,Interval and Date.

What I need is there will be bubble chart which has the values on X axis like 1-7 Jan 2009,8-15 Jan 2009,16-23 Jan 2009...24-31 Dec 2009.

Basically it is a seven day gap values.

Now the value of the bubble will be the Sum of Intervals

For Example: the first two rows have dates 01/01/2009,04/01/2009 Should come under 1-7 Jan 2009 and the

bubble value will be Sum(Intervals) of 01/01/2009,04/01/2009.

Please let me know if you have any doubts.

Regards

Navin

Generating Dates

Tue, 02 Aug 2011 05:59:42 GMT

Hi Navin ,

You can use MakeDate() method to generate date.

Ex:- MakeDate(2009,1,1) returns 2009-01-01

further now you can change the format of returned date like 1st jan 2009.

For more help about date function use 'using help' option in help menu in your qlikview.

Re: Generating Dates

Tue, 02 Aug 2011 06:25:22 GMT

Hi,

Let me know how to generate Week Start Dates as Rob Suggested Please.

Regards

Navin

Re: Generating Dates

Tue, 02 Aug 2011 06:27:10 GMT

Hi,

Let me know how to generate Week Start Dates as Rob Suggested Please.

Regards

Navin

Generating Dates

Tue, 02 Aug 2011 11:08:45 GMT

Hey,

I think this script will work

let curr_dt=MakeDate(2009,1,1);
let lst_dt=MonthEnd(curr_dt);

For d= '$(curr_dt)' To '$(lst_dt)'
let md=MakeDate(2009,1,Day(d)+6);
let dd=day(md)+1;
let d=makedate(2009,1,dd);
Load $(md) as Date;
Next d

Generating Dates

rwunderlich — Tue, 02 Aug 2011 14:09:39 GMT

Get a copy of the QV Cookbook from

http://robwunderlich.com/downloads/

Take a look at the example "Generate date (calendar) table.". That includes some typical patterns for generating dates. Dates are an integer. Incrementing a date by seven days is done by adding 7. There are also functions like WeekStart() that will identify the first day of the week.

-Rob

Re: Generating Dates

Wed, 03 Aug 2011 05:21:13 GMT

Hi Guys,

I will let you know what my actual requirement is...

I have columns like Job Date and PM Interval

Now I need to generate dates like 1st Jan 2009,10th Jan 2009,20th Jan 2009,....31st Dec 2009.

Dates have to be generated at an interval of 10 Days.I can do this date generation now.

I need to show a bubble chart which has these above generated dates in the x-axis and Pm Interval

in the y-axis.

Now, Suppose we have the foll data of Job Date and PM Interval

Job Date PmInterval

2-Jan-2009 30

4-Jan-2009 40

Now there will be two bubbles between 1st Jan 2009 and 10th Jan 2009 and at intersection point

of PMInterval 30 and 40.

Please guide me with this.

Regards

Navin.G

Re: Generating Dates

Wed, 03 Aug 2011 05:21:52 GMT

Hi Guys,

I will let you know what my actual requirement is...

I have columns like Job Date and PM Interval

Now I need to generate dates like 1st Jan 2009,10th Jan 2009,20th Jan 2009,....31st Dec 2009.

Dates have to be generated at an interval of 10 Days.I can do this date generation now.

I need to show a bubble chart which has these above generated dates in the x-axis and Pm Interval

in the y-axis.

Now, Suppose we have the foll data of Job Date and PM Interval

Job Date PmInterval

2-Jan-2009 30

4-Jan-2009 40

Now there will be two bubbles between 1st Jan 2009 and 10th Jan 2009 and at intersection point

of PMInterval 30 and 40.

Please guide me with this.Let me know if you have any issues.

Regards

Navin.G

Re: Generating Dates

Wed, 03 Aug 2011 08:52:17 GMT

Hi,

Please see the attached sample can generate every 10th day in a year.

Cheers.