topic Re: What is going on with my Master Calendar? Incorrect Dates in QlikView

What is going on with my Master Calendar? Incorrect Dates

neena123 — Sat, 16 Nov 2024 20:57:31 GMT

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:

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?

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!

Re: What is going on with my Master Calendar? Incorrect Dates

kushalthakral — Wed, 01 May 2019 09:24:57 GMT

Is it possible for you to share sample qvw file it is very hard to look for the solution

Thanks

Kushal

Re: What is going on with my Master Calendar? Incorrect Dates

sunny_talwar — Wed, 01 May 2019 11:46:14 GMT

Something is not adding up here... if this is the script you are using... then the first row should never happen

Because when the DataType = 'Entry', then Date and entrydate should match, but in your case, Date is matching rewarddate.... can you double/triple check your script to make sure that it is what you have shared?

Re: What is going on with my Master Calendar? Incorrect Dates

neena123 — Wed, 01 May 2019 13:31:04 GMT

No that is my script. Could it be because the both dates are from the same table?

Re: What is going on with my Master Calendar? Incorrect Dates

sunny_talwar — Wed, 01 May 2019 13:48:59 GMT

As far as I understand Qlik, it can't unless you have found a way to break the logic :). Jokes apart, may be share the logfile.

Re: What is going on with my Master Calendar? Incorrect Dates

neena123 — Wed, 01 May 2019 15:55:35 GMT

Is this enough data? I shared some of the report not everything but it has enough data to work with the master calendar. Let me know if it isn't enough. Thanks!

Re: What is going on with my Master Calendar? Incorrect Dates

sunny_talwar — Wed, 01 May 2019 15:59:01 GMT

The shared dashboard have no data at all.. Can you share PBDRapidTEST.xlsx?

Re: What is going on with my Master Calendar? Incorrect Dates

neena123 — Wed, 01 May 2019 16:06:48 GMT

Sorry! Don't know what happened there!

Re: What is going on with my Master Calendar? Incorrect Dates

sunny_talwar — Wed, 01 May 2019 16:22:26 GMT

I think your key (estimatenumber) alone is not good enough... I added arsalesname to the key and it looks better.... but you might need to add more fields to make the connection unique between your data and datelink table

Re: What is going on with my Master Calendar? Incorrect Dates

sunny_talwar — Wed, 01 May 2019 16:34:52 GMT

Sorry, I forgot to attach the dashboard

Re: What is going on with my Master Calendar? Incorrect Dates

neena123 — Wed, 01 May 2019 16:46:03 GMT

Thanks!!! It looks better! But I am still confused. In this table below I should only be showing BID that happened in January and JOBs in January. Bid are based of the entry date. But there are entrydates that are in December and are showing up after I have selected January. Am I missing something?

Re: What is going on with my Master Calendar? Incorrect Dates

sunny_talwar — Wed, 01 May 2019 17:04:17 GMT

Like I said, a better key is needed... try this

DateLink:
LOAD arsalesname,
estimatenumber,
rewarddate,
entrydate,
Date(rewarddate,'DD-MM-YYYY') as Date,
'Reward' as DateType
Resident Estimate
Where rewarddate <> '-';

Concatenate(DateLink)
LOAD arsalesname,
estimatenumber,
rewarddate,
entrydate,
Date(entrydate,'DD-MM-YYYY') as Date,
'Entry' as DateType
Resident Estimate
Where entrydate <> '-';