Problem with calendar

[qlikconsultant]

Hi,

I have the following Code:

Sales
LOAD i_ticket,
     date(d_date,'DD.MM.YYYY') as d_date,
     Sale
  FROM

(qvd);


Concatenate
Bonus:
LOAD i_ticket,
     date(d_date,'DD.MM.YYYY') as d_date,
     Bonus
FROM

(qvd);

and a calendar:

//-----------------------------------------------------------------------------------------------------------------------------
LET vMinBetDate = num('01.01.2016');
LET vMaxBetDate = today()-1;


TempCalendar_Betday: 
LOAD 
               $(vMinBetDate) + Iterno()-1                     as TempNum,
               Date($(vMinBetDate) + IterNo() - 1)             as TempDate 
               AutoGenerate 1 While $(vMinBetDate) + IterNo()-1 <= $(vMaxBetDate);


Calendar_Betday: 
Load 
               date(TempDate,'DD.MM.YYYY')                            as d_date,
               weekday(TempDate)                                    as Weekday, 
               week(TempDate)                                         as Week, 
               Year(TempDate)                                          as Year, 
               Month(TempDate)                                       as Month, 
               weekyear(TempDate)*100+week(TempDate)                 as YearCW,
               year(TempDate)*100+month(TempDate)                     as YearMn,
               num(month(TempDate))                                 as MonthNr

Resident TempCalendar_Betday
Order By TempDate ASC; 

drop table TempCalendar_Betday;

////-----------------------------------------------------------------------------------------------------------------------------

My problem is if i select a Month or MonthNr I get only i get only data from table Sales.

[Gysbert_Wassenaar]

Perhaps Bonus doesn't contain any dates from 2016. Or perhaps the values in its d_date field are not dates but text values.

[Clever_Anjos]

Please run only this piece of code and inspect the values of d_date

1. Bonus: 
2. LOAD i_ticket, 
3.      date(d_date,'DD.MM.YYYY') as d_date, 
4.      Bonus 
5. FROM 
6.  
7. (qvd); 

[qlikconsultant]

d_date was a timestamp.

Thanks for the help.

[Clever_Anjos · Accepted solution]

Use this instead

1. Bonus:
2. LOAD i_ticket,
3.      date(floor(d_date),'DD.MM.YYYY') as d_date,
4.      Bonus
5. FROM
7. (qvd);

Correct Answer • Reply