Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Partner
Partner

Master Calendar incorrect values. Help!

Hello!

 I have posted a couple questions that are centered around my dates in my expressions. I now realize I think my master calendar is messed up. I have two dates that I have linked together and created a master calendar please see below:

Calendar.PNG

 

I noticed when I went to select month of Jan to see how many BIDS and JOBS were in the month of Jan I noticed the Date field is all messed up.  How do I fix this? 

Table.PNG

Here is my Calendar Script 

DateLink:
LOAD Distinct
estimatenumber,
Date(entrydate,'DD-MM-YYYY') as Date,
'Entry' as DateType
Resident Estimate
//WHERE rewarddate <> null()
;
Concatenate(DateLink)
LOAD Distinct
estimatenumber,
Date(rewarddate,'DD-MM-YYYY') as Date,
'Reward' as DateType
Resident Estimate
Where rewarddate <> null()
;

Minmaxdates:
LOAD
min([Date]) as mindate,
max([Date]) as maxdate
Resident [DateLink];

Let vMinDate = Num(Peek('mindate',0,'minmaxdates'));
Let vMaxDate = Num(Peek('maxdate',0,'minmaxdates'));

DROP table Minmaxdates;

DateRanges:
LOAD
$(vMinDate) + IterNo()-1 as dateNum,
Date($(vMinDate) + IterNo()-1) as Date
AutoGenerate 1 While $(vMinDate) + IterNo()-1 <= $(vMaxDate);

MasterCalendar:
LOAD
Date as [Date],
Day(Date) as Day,
Week(Date) as Week,
Month(Date) as Month,
'Q' & Ceil(Month(Date)/3) as Quarter,
Year(Date) as Year
Resident DateRanges Order by Date asc;

DROP Tables DateRanges;

 

Thanks!

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1 Reply
krishna_2644
Valued Contributor III

Re: Master Calendar incorrect values. Help!

Pl post a mock up.