Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
neena123
Partner - Creator
Partner - Creator

2 Dates from the same table. Can I create a master calendar?

Hello!! 

 

I have read many documents and tried to figure out this myself but I need a little extra help...

So I have this table called Estimate. Now for every estimate put into the system there is an Entry Date also known as the "Bid Date" now once that Bid is accepted then there is a Reward Date. Both dates are located under the Estimate file

I am creating a report that has every detail on the estimate. I would like for my end user to have the ability to select the month Feb. and see all the Entry Dates made for that month as well as the older Entry Dates that now have Reward Dates.

 

What my table is doing now when I select Feb as the month, it shows All Entry Dates in Feb and  Reward Dates in Feb. that has Entry Dates In Feb. For example the top image is what my report is doing now and below that is what I want it to do. Any help is appreciated!! Thanks!Incorrect.PNG

 

 

 

What I want is below:

Correct.PNG

Labels (5)
9 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Please see this tutorial: Qlikview Cookbook: Tutorial - Using Common Date Dimensions http://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/

The downloads are available in both Qlik Sense and QlikView formats. 

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

neena123
Partner - Creator
Partner - Creator
Author

Hello! 

I tried that and when I went to select Feb I was showing dates in the wrong months.

neena123
Partner - Creator
Partner - Creator
Author

Do you have an idea as to why? The Common date that I created is making up a date filed that doesn't apply.  You can see below the common date field has generated the date 2-13-2019 but the entry date and reward date do not have a date in Feb. So why is it displaying? error.PNG

 

 

                                                  script.PNG

 

                                                                         

 

                                                                            

 

                                                            

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

I can't tell from your screenshot. Can you upload a sample qvw?

-Rob

neena123
Partner - Creator
Partner - Creator
Author

Hi Rob, Before I upload a sample qvw I just wanted to ask if the reason why it isn't working is because I am bringing in 2 QVDs then joining the files and then creating my master calendar. The reason why I have separate QVDs is because the data fields are the exact same but the values are different because of different sources. 

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Hard to say without seeing your script.  Are you concatenating the QVDs or joining them?

neena123
Partner - Creator
Partner - Creator
Author

[Estimate]:
LOAD estimate,
estimatepartid,
salespersonid,
description1,
estimatenumber,
description,
customerprospectname,
enteredby,
duedate,
entrytime,
entrydate,
estimaterequest,
estimateversionnumber,
estimatorid,
lastjob,
rewarddate,
estimatequantityid,
markup,
markuppercent,
cost,
nonvalueaddedmarkup,
nonvalueadded,
overallmarkup,
price,
quotedprice,
quantityordered,
valueaddedmarkup,
valueadded,
valueaddedprice,
valueaddedpercent,
arsalesname,
[Estimate Part]
FROM
[D:\Client View\PRT\PRTI.qvd]
(qvd);


JOIN(Estimate)
LOAD estimate,
estimatepartid,
salespersonid,
description1,
estimatenumber,
description,
customerprospectname,
enteredby,
duedate,
entrytime,
entrydate,
estimaterequest,
estimateversionnumber,
estimatorid,
lastjob,
rewarddate,
estimatequantityid,
markup,
markuppercent,
cost,
nonvalueaddedmarkup,
nonvalueadded,
overallmarkup,
price,
quotedprice,
quantityordered,
valueaddedmarkup,
valueadded,
valueaddedprice,
valueaddedpercent,
arsalesname,
[Estimate Part]
FROM
[D:\Client View\RPT\RPTI.qvd] (qvd);

DateLink:
LOAD Distinct
estimatenumber,
rewarddate as Date
Resident Estimate
;

Concatenate(DateLink)
LOAD Distinct
estimatenumber,
entrydate as Date
Resident Estimate ;
Calendar:
LOAD

Date,
Month(Date) AS Month,
Year(Date) AS Year,
Day(Date) AS Day
Resident DateLink;

 

 

 

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Loading of RPTI should not be JOIN(Estimate).  It should be:

CONCATENATE(Estimate)

CONCATENATE will append additional rows to the table which is what you want. 

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

neena123
Partner - Creator
Partner - Creator
Author

Thanks Rob but I tried that and it is giving me the wrong dates still