Here is a Master Calendar you can use (or at least the expressions)
You need create the variables varMaxDate and varMinDate
By any chance are you using hours/minutes in your dates? Becasuse QV represents differently a same date with 2 different hours.
$(varMinDate) + rowno() - 1 AS Num,
date($(varMinDate) + rowno() - 1) AS TempDate
$(varMaxDate) - $(varMinDate) + 1;
//*************** Master Calendar ***************
LOAD TempDate AS PeriodEnd ,
Week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
Weekday(TempDate) AS WeekDay,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Date(monthstart(TempDate), 'MMM-YYYY') AS
Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
inyeartodate(TempDate, $(vToday), 0) * -1 AS
inyeartodate(TempDate, $(vToday), -1) * -1 AS
ORDER BY TempDate ASC;
DROP TABLE TempCalendar;
Thanks a lot for your response. I'm kind of novice, so I don't really know what to do with the tempcalendar or master calendar, or how to create the variables varMaxDate and varMinDate.
There is a timestamp, but it's the same on every line. It shows me 12:00:00 AM, this isn't preset, I think it's being added by QlikView, and I'm removing it with the numbering tab.
A good use for a master calendar is when your date doesnt necesary has every date in a year and you want to represent a calenar with list tables. Here you ensure that you will get 1 to 31 in days and January to December in months. Also, every date will correspond to a month, year, quarter, week, weekday, etc.
varMaxDate and varMinDate is for the range of your calendar. Normally you peek for the min and max date of your data.
About the repetition of dates, could you upload your qvw to take a look?