topic Re: Selecting two dates merging into one date in QlikView

Selecting two dates merging into one date

provie98 — Sat, 16 Nov 2024 03:29:20 GMT

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?

Re: Selecting two dates merging into one date

m_woolf — Thu, 23 May 2019 18:32:09 GMT

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

Re: Selecting two dates merging into one date

provie98 — Thu, 23 May 2019 19:30:30 GMT

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.

Re: Selecting two dates merging into one date

rwunderlich — Thu, 23 May 2019 21:31:14 GMT

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

-Rob

Re: Selecting two dates merging into one date

provie98 — Fri, 24 May 2019 14:03:04 GMT

Attached is the data structure. Thanks for your help.

Re: Selecting two dates merging into one date

provie98 — Fri, 24 May 2019 14:16:04 GMT

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.

Re: Selecting two dates merging into one date

rwunderlich — Fri, 24 May 2019 15:14:52 GMT

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

Re: Selecting two dates merging into one date

provie98 — Fri, 24 May 2019 16:57:34 GMT

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.

Re: Selecting two dates merging into one date

rwunderlich — Fri, 24 May 2019 22:15:28 GMT

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