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: 
provie98
Contributor II
Contributor II

Selecting two dates merging into one date

It's been awhile since I've played with Qlikview so please ignore my ignorance.  I have the following:

Customer Table

  • Cust_ID
  • Customer_Open_Date

Deposits Table

  • Cust_ID
  • Deposit_Date_Added

I'm trying to create ONE  master calendar so when I click Year it filters on both the Customer Open Date  AND the Deposit Date Added.   For Example, I want to look at accounts that have customers opened in January 2019 AND ALSO Deposits Opened in January 2019.  I don't want to have Customers Opened In January 2019 and Deposits In February 2019.  In SQL I would write the statement like this:

Select *

From Customers inner join Deposits on Customers.Cust_ID = Deposits.Cust_ID where Customers.Customer_Open_Date Between '01/01/2019' and '01/31/2019'  AND Deposits.Deposit_Date_Added Between '01/01/2019' and '01/31/2019' 

When loading the data into click I take out the Where clause because I want to allow the user to use a date range for both fields.  Any idea how to do this?  I have already tried the Canonical Date approach unsuccessfully.  It's not bringing me back the results I want.  Any other idea how to make this happen?

 

Labels (1)
1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

If you want an AND for both dates in the selected date range, you can use a set expression like:

count({<Add_Date=P(Date), Cust_OpenDate_Of_Oldest_Account=P(Date)>}Cust_Name_Key)

This will filter to accounts in the same month if you select one month.  If you select two months, it means the activity occurred in one of the months, but not necessarily the same month for a given account. 

If you want only those accounts where the activity happened in the same month, you could modify as:

=count({<Add_Date=P(Date), Cust_OpenDate_Of_Oldest_Account=P(Date),
Cust_Name_Key={"=monthstart(Add_Date)=monthstart(Cust_OpenDate_Of_Oldest_Account)"}>}
DISTINCT Cust_Name_Key)

If you wanted to do that kind of month matching, it's probably best to do it in the script and set a flag for those accounts where the month is equal. 

-Rob

 

 

 

View solution in original post

8 Replies
m_woolf
Master II
Master II

Look at Rob Wunderlich's  Tutorial - Using Common Date Dimensions 
https://qlikviewcookbook.com/list-recipes/#squelch-taas-accordion-shortcode-content-17

provie98
Contributor II
Contributor II
Author

Thanks, I actually downloaded that as example to use but unfortunately, it's not working in my Qlik model.  When I select a January 2019 from my common date field, it's not bringing back just January 2019 dates for Deposits Date Added and January 2019 Dates for Customer Open Date.  When I select the the two fields separately it's showing correctly.  

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

It looks to me like you are not building the link table correctly. Can you post a screenshot of the table model?

-Rob

provie98
Contributor II
Contributor II
Author

Attached is the data structure.  Thanks for your help.

provie98
Contributor II
Contributor II
Author

I think this maybe useful to show you as well.  The Date field is the Field on the Common Calendar.  I'm selecting January 2019.  But when you look at the Cust Open Date and Date Added, they don't pull January.  Perhaps it maybe my data isn't setup correctly to do the common calendar.  I just thought you should see it.  I have account number in the detail as well as there are different account numbers for different products linked by Cust ID.  I can't show those due to confidentiality.  Thanks again for taking the time to help out.  I really appreciate it. 

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

It looks like your link table is not set up correctly.   Check the subset ratios between your keys and see if anything is matching up.  If possible, post a reduced and scrambled qvw sample so we can take a look.

-Rob

provie98
Contributor II
Contributor II
Author

I switched some things around and tried it a different way.  It's still giving me the same results when I click January 2019 in the common calendar. If I chose 2019 in the Deposit Year and Customer Year and January in the Deposit Month and Customer Month, it brings me back the correct results.  

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

If you want an AND for both dates in the selected date range, you can use a set expression like:

count({<Add_Date=P(Date), Cust_OpenDate_Of_Oldest_Account=P(Date)>}Cust_Name_Key)

This will filter to accounts in the same month if you select one month.  If you select two months, it means the activity occurred in one of the months, but not necessarily the same month for a given account. 

If you want only those accounts where the activity happened in the same month, you could modify as:

=count({<Add_Date=P(Date), Cust_OpenDate_Of_Oldest_Account=P(Date),
Cust_Name_Key={"=monthstart(Add_Date)=monthstart(Cust_OpenDate_Of_Oldest_Account)"}>}
DISTINCT Cust_Name_Key)

If you wanted to do that kind of month matching, it's probably best to do it in the script and set a flag for those accounts where the month is equal. 

-Rob