Hello,
It's not a dimensional model. I could create 2 queries, I am hoping to figure out how to do it in just one.
Create Date represents the date an initial contact was made.
Open Date represents if the contact then opened an account.
So, in the below example, I need to count 'account123' for January (based on the Create Date) and again in March (based on the Open Date).
In 'account456' I only need to count in January. I need to combine the months to show on the axis.
example rows:
ID Create Date Open Date
account123 1-1-2020 3-1-2020
account456 1-2-2020 Null
------------------------------
Jenifer Broughton
------------------------------
Original Message:
Sent: Tue October 27, 2020 08:23 AM
From: Sandeep Dhirad
Subject: Count based on 2 different dates
If it is dimensional modeled, I would create a join between period dimension on create date and an alias of the fact joined with open date.
For the create month count, you only need to count account ID, assuming the ID is unique.For the alias fact it would be the same. If all records have open date, so that actually closed have dummy dates, you can filter them out or create a case statement. If the closed have NULL instead of open date, it should work with a inner join.
/Sandeep
------------------------------
Sandeep Dhirad
Elkjøp Nordic
Original Message:
Sent: Mon October 26, 2020 02:52 PM
From: Jenifer Broughton
Subject: Count based on 2 different dates
Hello,
I want to create a graph with month on my axis. I need to count account id for create date (month), but I also need to count the account id if it has an open date (month). The create and open date months can be different.
Any ideas?
Thanks,
Jen
------------------------------
Jenifer Broughton
------------------------------
#CognosAnalyticswithWatson