Discussion Board for collaboration on QlikView Layout & Visualizations.
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.
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)
$(Y) AS Year,
$(M) AS Month;
$(D) AS Day,
makedate($(Y),$(M),$(D)) as Date
Hope this helps....
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.
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.
For that you may use:
Hope this Helps...
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.
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.
Let me know how to generate Week Start Dates as Rob Suggested Please.
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