I have added calendar script in the data editor, referred from the date and time blog (below). Is this what you mean?
Declare Field Definition Tagged '$date'
first_month_of_year = 1
Year($1) as Year Tagged '$year',
Month($1) as Month Tagged '$month',
Date($1) as Date Tagged ('$date', '$day'),
Week($1) as Week Tagged '$week'
Year, Month, Date type collection as YearMonthDate;
Derive Fields from Fields ExpenseDate Using Calendar;
no, that script won't populate missing dates, months... it just forms the common date buckets that most people want from a date field.
Here is a sample below where i loop between the years of the min and max date and create a table that links to the main table with dates.
load * inline [
Date(Date#(Month,'YYYYMM')) as Date,
drop table RawData;
load distinct Date(min(Date)) as minDate, Date(max(Date)) as maxDate resident SalesData;
drop table MinMaxTable;
for vYear=0 to $(vYears)
makedate( $(vMakeYear),recno()) as Date
Test2.qvf 176.0 K
Not certain i understand. You can certainly manufacture some random data in Qlik to provide many more rows using the rand() function and the autogenerate command in a load. The following randomizes dates over the last 10 years and randomizes sales numbers from 0 to 1000 each. Is that what you are looking for ?
makedate( floor(rand()*10) + year(today())-9, floor(rand() * 12)+1 ) as Date,
num(rand()*1000,'#,###.#0') as Sales
Year(Date) as Year,
Month(Date) as Month
Sorry, let me explain more clearly. Your code in the previous response seems to have two sections; RawData and SalesData. And here's my understanding/questions:
RawData are dates with available sales data. But how can I easily put these data into the editor if there are so many of them?
Also my corresponding dimensions and measures are Month and sum(Amount), respectively. For 'SalesData:' in your code, would it be correct to put AmountData? Which ones must I customize to my dataset?
Thanks so much!
Please take a look at this video it should explain how to create and use a master calendar and solve your problem.
In regards to what you are using that is not a really good example of a master calendar.
It does not fill in all of the missing data it just derives New fields from existing dates
Please mark the appropriate discussion thread as helpful or correct so our team knows that your question has been answered to your satisfaction
I tried with the instruction above and I get the following error
The error occurred here:
Temp: Load min(ClosingDate) as minDate, max(ClosingDate) as maxDate Resident Pipeline 05 29 2015 v.2
The date field is called 'Closing Date' and it shows as [Closing Date] on the default main data load editor. I tried using the same format [ ] and it still does not work. Also, for the Pipeline 05 29 2015 v.2 (next to 'Resident') is the name of the file. Am i supposed to put the name of the file next to 'Resident'?