Discussion Board for collaboration on QlikView Scripting.
I have a field name: Requested Date, I want this field name to be the basis of my calendar.
Would it be possible to create a calendar from which minimum date and maximum date is extracted from Requested Date?
Need help from you guys, thanks.
Here is the code, might exist some syntax error because I directly input code here, instead of QV
min([Requested Date]) as minDate,
max([Requested Date]) as maxDate
resident [your table name];
let v_min = FieldValue('minDate',1);
let v_max = FieldValue('maxDate',1);
for i = Year($(v_min)) to Year($(v_max)) //loop for year
for j = 1 to 12 //loop for month
let v_month = AddMonths(Date#($(i),'YYYY'), $(j)-1); //get a DATE variable, value= 1st day of that month
for k = 1 to day(MonthEnd($(v_month))) //loop for day, using day(monthend()) to get the largest day
//connect $i, $j, $k as a string, formatted as YYYY-MM-DD
let v_date_str = $(i) & '-' & $(j) & '-' & $(k);
let v_date = Date#($(v_date_str),'YYYY-MM-DD'); conver it as a date type
//load this variable into your calendar table
Load * inline [
I made a mistake. Above code is "create calendar table from the beginning of min year to end of max year“.
min date is 2010-08-02, max date is 2010-09-31
The rows in calendar table will be 2010-01-01 to 2010-12-31
So if you just need "date between min & max date", try below one:
for i = $(v_min) to $(v_max) //loop for every day
//above row might convert value from date to int, so convert it back to date
let v_date = Date#($(i),'YYYY-MM-DD')
Same as above code, has not executed in QV so might exist syntax error, I guess you can fix it by your self.
BTW, I don't think it is necessary to create a calendar table. If your date in your fact table is not continously, try edit your chart (only workable for chart, instead of table) by this way:
3. enable "continuous"
Thanks. I already did it! Would you mind, do you know how to get quarter?
I forgot to include, is this possible? To get the weeknumbers per month? rather than per year?
Example: I want the output like this
Month Week No.
Anyone can help? Or is this really possible?
you can get quarter using 3 technique:
1. Inline load
2. Loading from excel
3. Using applymap() function
here is attached sample application:
Hi Arun, thanks for the info.