Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Generating Dates

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

13 Replies
rahulgupta
Partner - Creator III
Partner - Creator III

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Not applicable
Author

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

rahulgupta
Partner - Creator III
Partner - Creator III

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

Not applicable
Author

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     

Not applicable
Author

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.

Not applicable
Author

Hi,

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

Regards

Navin

Not applicable
Author

Hi,

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

Regards

Navin

Not applicable
Author

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