Cognos Analytics

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

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

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

Hi Jennifer

One way to do this is by creating a <union>, with <preserve duplicates> so that the data looks like:

ID                            Activity Date
account123                    3-1-2020
account456                    Null
account123                    1-1-2020
account456                    1-2-2020

You can then create a visualization for _month(Acitivity Date)  and count(Activity Date)

// Henk

------------------------------
HENK CAZEMIER
------------------------------

Original Message:
Sent: Tue October 27, 2020 08:37 AM
From: Jenifer Broughton
Subject: Count based on 2 different dates

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

Hi Jenifer,

the easy way to achieve this is to create 2 querys an then union them in to a new query.
because account123 has to be in both querys because it has 2 dates

QUERY 1
'CREATED'     AS NAME
ID                   AS ID
Create Date   AS DATE

QUERY 2
'OPEN'           AS NAME
ID                   AS ID
open Date      AS DATE

------------------------------
Bas Schuurmans
------------------------------

Original Message:
Sent: Tue October 27, 2020 08:37 AM
From: Jenifer Broughton
Subject: Count based on 2 different dates

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

Hi Jen,
So, I tinkered around with this, you might be able to use this approach in a single query:
1. Create a calc that sums up all the account id's in a create date month: total (if([Create Date] is not NULL) then (1) else (0) for [Month])
2. Create a calc that sums up all the account id's in a open date month: total (if([Open Date] is not NULL) then (1) else (0) for [Month])
3. Sum those values and use this as your measure

This gets trickier if you are traversing multiple years, but I think you just need to add the [Year] item to your for clause "for [Year], [Month]"

------------------------------
Albert Valdez
albert@intelalytics.com
------------------------------

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

Hello,

Thanks Henk.  I am hoping to get this in one query and avoid a union. 

Albert - Thank you for this approach.  I'm going to give this and try.  Thankfully, I am only interested in the 'current year' so I hope your idea works.  I will update you once I try it today!

Thanks Everyone!​

------------------------------
Jenifer Broughton
------------------------------

Original Message:
Sent: Wed October 28, 2020 01:39 PM
From: Albert Valdez
Subject: Count based on 2 different dates

Hi Jen,
So, I tinkered around with this, you might be able to use this approach in a single query:
1. Create a calc that sums up all the account id's in a create date month: total (if([Create Date] is not NULL) then (1) else (0) for [Month])
2. Create a calc that sums up all the account id's in a open date month: total (if([Open Date] is not NULL) then (1) else (0) for [Month])
3. Sum those values and use this as your measure

This gets trickier if you are traversing multiple years, but I think you just need to add the [Year] item to your for clause "for [Year], [Month]"

------------------------------
Albert Valdez
albert@intelalytics.com

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

​Just an update.

I still am unable to figure out how to get the totals under a common month.

ID                                  Create Date                  Open Date
account123                    1-1-2020                       3-1-2020
account456                    1-2-2020                       Null


So I want a graph that shows January = 2 and March = 1
I the months on each row won't necessarily be the same.  So account123 will be counted under a consolidated January and then also again under the consolidated March bucket on the axis.

------------------------------
Jenifer Broughton
------------------------------

Original Message:
Sent: Thu October 29, 2020 09:33 AM
From: Jenifer Broughton
Subject: Count based on 2 different dates

Hello,

Thanks Henk.  I am hoping to get this in one query and avoid a union. 

Albert - Thank you for this approach.  I'm going to give this and try.  Thankfully, I am only interested in the 'current year' so I hope your idea works.  I will update you once I try it today!

Thanks Everyone!​

------------------------------
Jenifer Broughton

Original Message:
Sent: Wed October 28, 2020 01:39 PM
From: Albert Valdez
Subject: Count based on 2 different dates

Hi Jen,
So, I tinkered around with this, you might be able to use this approach in a single query:
1. Create a calc that sums up all the account id's in a create date month: total (if([Create Date] is not NULL) then (1) else (0) for [Month])
2. Create a calc that sums up all the account id's in a open date month: total (if([Open Date] is not NULL) then (1) else (0) for [Month])
3. Sum those values and use this as your measure

This gets trickier if you are traversing multiple years, but I think you just need to add the [Year] item to your for clause "for [Year], [Month]"

------------------------------
Albert Valdez
albert@intelalytics.com

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

Hi Jenifer,

What about something like this...

Create two new items called MonthCreate and MonthClose and use the function extract to pull out the month number.  extract(Month, [CreateMonth] or [OpenDate]).  Then do a count on each to sum them up.  Count(MonthCreate or MonthClose for ID).  That will give you a consolidated count for each.  You can then add them to a chart and see the totals for each month.


------------------------------
Todd Schuman
------------------------------

Original Message:
Sent: Fri October 30, 2020 08:04 AM
From: Jenifer Broughton
Subject: Count based on 2 different dates

​Just an update.

I still am unable to figure out how to get the totals under a common month.

ID                                  Create Date                  Open Date
account123                    1-1-2020                       3-1-2020
account456                    1-2-2020                       Null


So I want a graph that shows January = 2 and March = 1
I the months on each row won't necessarily be the same.  So account123 will be counted under a consolidated January and then also again under the consolidated March bucket on the axis.

------------------------------
Jenifer Broughton

Original Message:
Sent: Thu October 29, 2020 09:33 AM
From: Jenifer Broughton
Subject: Count based on 2 different dates

Hello,

Thanks Henk.  I am hoping to get this in one query and avoid a union. 

Albert - Thank you for this approach.  I'm going to give this and try.  Thankfully, I am only interested in the 'current year' so I hope your idea works.  I will update you once I try it today!

Thanks Everyone!​

------------------------------
Jenifer Broughton

Original Message:
Sent: Wed October 28, 2020 01:39 PM
From: Albert Valdez
Subject: Count based on 2 different dates

Hi Jen,
So, I tinkered around with this, you might be able to use this approach in a single query:
1. Create a calc that sums up all the account id's in a create date month: total (if([Create Date] is not NULL) then (1) else (0) for [Month])
2. Create a calc that sums up all the account id's in a open date month: total (if([Open Date] is not NULL) then (1) else (0) for [Month])
3. Sum those values and use this as your measure

This gets trickier if you are traversing multiple years, but I think you just need to add the [Year] item to your for clause "for [Year], [Month]"

------------------------------
Albert Valdez
albert@intelalytics.com

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

Hi Todd.  This is exactly what I am looking for.  Can you share the 'properties' and the data container set up as well?  I don't see where I can add multiple fields to the values.

Thanks,
Jen​

------------------------------
Jenifer Broughton
------------------------------

Original Message:
Sent: Mon November 02, 2020 08:16 AM
From: Todd Schuman
Subject: Count based on 2 different dates

Hi Jenifer,

What about something like this...

Create two new items called MonthCreate and MonthClose and use the function extract to pull out the month number.  extract(Month, [CreateMonth] or [OpenDate]).  Then do a count on each to sum them up.  Count(MonthCreate or MonthClose for ID).  That will give you a consolidated count for each.  You can then add them to a chart and see the totals for each month.

I used the go sales data and product intro and discontinued date to build the sample below.

------------------------------
Todd Schuman

Original Message:
Sent: Fri October 30, 2020 08:04 AM
From: Jenifer Broughton
Subject: Count based on 2 different dates

​Just an update.

I still am unable to figure out how to get the totals under a common month.

ID                                  Create Date                  Open Date
account123                    1-1-2020                       3-1-2020
account456                    1-2-2020                       Null


So I want a graph that shows January = 2 and March = 1
I the months on each row won't necessarily be the same.  So account123 will be counted under a consolidated January and then also again under the consolidated March bucket on the axis.

------------------------------
Jenifer Broughton

Original Message:
Sent: Thu October 29, 2020 09:33 AM
From: Jenifer Broughton
Subject: Count based on 2 different dates

Hello,

Thanks Henk.  I am hoping to get this in one query and avoid a union. 

Albert - Thank you for this approach.  I'm going to give this and try.  Thankfully, I am only interested in the 'current year' so I hope your idea works.  I will update you once I try it today!

Thanks Everyone!​

------------------------------
Jenifer Broughton

Original Message:
Sent: Wed October 28, 2020 01:39 PM
From: Albert Valdez
Subject: Count based on 2 different dates

Hi Jen,
So, I tinkered around with this, you might be able to use this approach in a single query:
1. Create a calc that sums up all the account id's in a create date month: total (if([Create Date] is not NULL) then (1) else (0) for [Month])
2. Create a calc that sums up all the account id's in a open date month: total (if([Open Date] is not NULL) then (1) else (0) for [Month])
3. Sum those values and use this as your measure

This gets trickier if you are traversing multiple years, but I think you just need to add the [Year] item to your for clause "for [Year], [Month]"

------------------------------
Albert Valdez
albert@intelalytics.com

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

Hi Jenifer,

There is nothing special in the properties or data containers, just a list and 11.1 line chart viz. 



What version are you running?  I am on 11.1.7 FP1.  I believe you would need at least 11.1.6 to take advantage of the multiple measures on a visualization enhancement that was added.

Cognos 11.1.6: Our Favorite New Features - Senturus Blog

If you are on an earlier version and/or can't upgrade, you would have to break it up into two queries and union them together with an additional text field that described the date as "Create Date" or "Open Date".  You would then use that field to drive the color or category.

I can try to mock something up this way if you need more help.

-Todd

------------------------------
Todd Schuman
------------------------------

Original Message:
Sent: Mon November 02, 2020 10:30 AM
From: Jenifer Broughton
Subject: Count based on 2 different dates

Hi Todd.  This is exactly what I am looking for.  Can you share the 'properties' and the data container set up as well?  I don't see where I can add multiple fields to the values.

Thanks,
Jen​

------------------------------
Jenifer Broughton

Original Message:
Sent: Mon November 02, 2020 08:16 AM
From: Todd Schuman
Subject: Count based on 2 different dates

Hi Jenifer,

What about something like this...

Create two new items called MonthCreate and MonthClose and use the function extract to pull out the month number.  extract(Month, [CreateMonth] or [OpenDate]).  Then do a count on each to sum them up.  Count(MonthCreate or MonthClose for ID).  That will give you a consolidated count for each.  You can then add them to a chart and see the totals for each month.

I used the go sales data and product intro and discontinued date to build the sample below.

------------------------------
Todd Schuman

Original Message:
Sent: Fri October 30, 2020 08:04 AM
From: Jenifer Broughton
Subject: Count based on 2 different dates

​Just an update.

I still am unable to figure out how to get the totals under a common month.

ID                                  Create Date                  Open Date
account123                    1-1-2020                       3-1-2020
account456                    1-2-2020                       Null


So I want a graph that shows January = 2 and March = 1
I the months on each row won't necessarily be the same.  So account123 will be counted under a consolidated January and then also again under the consolidated March bucket on the axis.

------------------------------
Jenifer Broughton

Original Message:
Sent: Thu October 29, 2020 09:33 AM
From: Jenifer Broughton
Subject: Count based on 2 different dates

Hello,

Thanks Henk.  I am hoping to get this in one query and avoid a union. 

Albert - Thank you for this approach.  I'm going to give this and try.  Thankfully, I am only interested in the 'current year' so I hope your idea works.  I will update you once I try it today!

Thanks Everyone!​

------------------------------
Jenifer Broughton

Original Message:
Sent: Wed October 28, 2020 01:39 PM
From: Albert Valdez
Subject: Count based on 2 different dates

Hi Jen,
So, I tinkered around with this, you might be able to use this approach in a single query:
1. Create a calc that sums up all the account id's in a create date month: total (if([Create Date] is not NULL) then (1) else (0) for [Month])
2. Create a calc that sums up all the account id's in a open date month: total (if([Open Date] is not NULL) then (1) else (0) for [Month])
3. Sum those values and use this as your measure

This gets trickier if you are traversing multiple years, but I think you just need to add the [Year] item to your for clause "for [Year], [Month]"

------------------------------
Albert Valdez
albert@intelalytics.com

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

Hi Todd.  Thanks for the update.  Sadly, we are currently running 11.0.11.​  I appreciate your feedback, I can do it with a Union, but was hoping to figure out a way not to. 

Thanks again!!

------------------------------
Jenifer Broughton
------------------------------

Original Message:
Sent: Tue November 03, 2020 07:51 AM
From: Todd Schuman
Subject: Count based on 2 different dates

Hi Jenifer,

There is nothing special in the properties or data containers, just a list and 11.1 line chart viz. 

What version are you running?  I am on 11.1.7 FP1.  I believe you would need at least 11.1.6 to take advantage of the multiple measures on a visualization enhancement that was added.

Cognos 11.1.6: Our Favorite New Features - Senturus Blog

If you are on an earlier version and/or can't upgrade, you would have to break it up into two queries and union them together with an additional text field that described the date as "Create Date" or "Open Date".  You would then use that field to drive the color or category.

I can try to mock something up this way if you need more help.

-Todd

------------------------------
Todd Schuman

Original Message:
Sent: Mon November 02, 2020 10:30 AM
From: Jenifer Broughton
Subject: Count based on 2 different dates

Hi Todd.  This is exactly what I am looking for.  Can you share the 'properties' and the data container set up as well?  I don't see where I can add multiple fields to the values.

Thanks,
Jen​

------------------------------
Jenifer Broughton

Original Message:
Sent: Mon November 02, 2020 08:16 AM
From: Todd Schuman
Subject: Count based on 2 different dates

Hi Jenifer,

What about something like this...

Create two new items called MonthCreate and MonthClose and use the function extract to pull out the month number.  extract(Month, [CreateMonth] or [OpenDate]).  Then do a count on each to sum them up.  Count(MonthCreate or MonthClose for ID).  That will give you a consolidated count for each.  You can then add them to a chart and see the totals for each month.

I used the go sales data and product intro and discontinued date to build the sample below.

------------------------------
Todd Schuman

Original Message:
Sent: Fri October 30, 2020 08:04 AM
From: Jenifer Broughton
Subject: Count based on 2 different dates

​Just an update.

I still am unable to figure out how to get the totals under a common month.

ID                                  Create Date                  Open Date
account123                    1-1-2020                       3-1-2020
account456                    1-2-2020                       Null


So I want a graph that shows January = 2 and March = 1
I the months on each row won't necessarily be the same.  So account123 will be counted under a consolidated January and then also again under the consolidated March bucket on the axis.

------------------------------
Jenifer Broughton

Original Message:
Sent: Thu October 29, 2020 09:33 AM
From: Jenifer Broughton
Subject: Count based on 2 different dates

Hello,

Thanks Henk.  I am hoping to get this in one query and avoid a union. 

Albert - Thank you for this approach.  I'm going to give this and try.  Thankfully, I am only interested in the 'current year' so I hope your idea works.  I will update you once I try it today!

Thanks Everyone!​

------------------------------
Jenifer Broughton

Original Message:
Sent: Wed October 28, 2020 01:39 PM
From: Albert Valdez
Subject: Count based on 2 different dates

Hi Jen,
So, I tinkered around with this, you might be able to use this approach in a single query:
1. Create a calc that sums up all the account id's in a create date month: total (if([Create Date] is not NULL) then (1) else (0) for [Month])
2. Create a calc that sums up all the account id's in a open date month: total (if([Open Date] is not NULL) then (1) else (0) for [Month])
3. Sum those values and use this as your measure

This gets trickier if you are traversing multiple years, but I think you just need to add the [Year] item to your for clause "for [Year], [Month]"

------------------------------
Albert Valdez
albert@intelalytics.com

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

Hi Jenifer,

Why don't you use the good old charts? If it does not have to be a dashboard you can use the old charts and with a bit of customizing you will get almost the same lay-out.

------------------------------
Thomas van der Meer
------------------------------

Original Message:
Sent: Tue November 03, 2020 08:26 AM
From: Jenifer Broughton
Subject: Count based on 2 different dates

Hi Todd.  Thanks for the update.  Sadly, we are currently running 11.0.11.​  I appreciate your feedback, I can do it with a Union, but was hoping to figure out a way not to. 

Thanks again!!

------------------------------
Jenifer Broughton

Original Message:
Sent: Tue November 03, 2020 07:51 AM
From: Todd Schuman
Subject: Count based on 2 different dates

Hi Jenifer,

There is nothing special in the properties or data containers, just a list and 11.1 line chart viz. 

What version are you running?  I am on 11.1.7 FP1.  I believe you would need at least 11.1.6 to take advantage of the multiple measures on a visualization enhancement that was added.

Cognos 11.1.6: Our Favorite New Features - Senturus Blog

If you are on an earlier version and/or can't upgrade, you would have to break it up into two queries and union them together with an additional text field that described the date as "Create Date" or "Open Date".  You would then use that field to drive the color or category.

I can try to mock something up this way if you need more help.

-Todd

------------------------------
Todd Schuman

Original Message:
Sent: Mon November 02, 2020 10:30 AM
From: Jenifer Broughton
Subject: Count based on 2 different dates

Hi Todd.  This is exactly what I am looking for.  Can you share the 'properties' and the data container set up as well?  I don't see where I can add multiple fields to the values.

Thanks,
Jen​

------------------------------
Jenifer Broughton

Original Message:
Sent: Mon November 02, 2020 08:16 AM
From: Todd Schuman
Subject: Count based on 2 different dates

Hi Jenifer,

What about something like this...

Create two new items called MonthCreate and MonthClose and use the function extract to pull out the month number.  extract(Month, [CreateMonth] or [OpenDate]).  Then do a count on each to sum them up.  Count(MonthCreate or MonthClose for ID).  That will give you a consolidated count for each.  You can then add them to a chart and see the totals for each month.

I used the go sales data and product intro and discontinued date to build the sample below.

------------------------------
Todd Schuman

Original Message:
Sent: Fri October 30, 2020 08:04 AM
From: Jenifer Broughton
Subject: Count based on 2 different dates

​Just an update.

I still am unable to figure out how to get the totals under a common month.

ID                                  Create Date                  Open Date
account123                    1-1-2020                       3-1-2020
account456                    1-2-2020                       Null


So I want a graph that shows January = 2 and March = 1
I the months on each row won't necessarily be the same.  So account123 will be counted under a consolidated January and then also again under the consolidated March bucket on the axis.

------------------------------
Jenifer Broughton

Original Message:
Sent: Thu October 29, 2020 09:33 AM
From: Jenifer Broughton
Subject: Count based on 2 different dates

Hello,

Thanks Henk.  I am hoping to get this in one query and avoid a union. 

Albert - Thank you for this approach.  I'm going to give this and try.  Thankfully, I am only interested in the 'current year' so I hope your idea works.  I will update you once I try it today!

Thanks Everyone!​

------------------------------
Jenifer Broughton

Original Message:
Sent: Wed October 28, 2020 01:39 PM
From: Albert Valdez
Subject: Count based on 2 different dates

Hi Jen,
So, I tinkered around with this, you might be able to use this approach in a single query:
1. Create a calc that sums up all the account id's in a create date month: total (if([Create Date] is not NULL) then (1) else (0) for [Month])
2. Create a calc that sums up all the account id's in a open date month: total (if([Open Date] is not NULL) then (1) else (0) for [Month])
3. Sum those values and use this as your measure

This gets trickier if you are traversing multiple years, but I think you just need to add the [Year] item to your for clause "for [Year], [Month]"

------------------------------
Albert Valdez
albert@intelalytics.com

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

All,

I was able to figure it out, thanks to all the input!  I used the combination chart.  ​
Used the following calc's

Count Create = count([Create Date] for [MONTH CREATE])
Count Open = count([Date Open] for [MONTH OPEN])

Modified the aggregations and got what I needed.

THANKS for the help!

------------------------------
Jenifer Broughton
------------------------------

Original Message:
Sent: Tue November 03, 2020 08:26 AM
From: Jenifer Broughton
Subject: Count based on 2 different dates

Hi Todd.  Thanks for the update.  Sadly, we are currently running 11.0.11.​  I appreciate your feedback, I can do it with a Union, but was hoping to figure out a way not to. 

Thanks again!!

------------------------------
Jenifer Broughton

Original Message:
Sent: Tue November 03, 2020 07:51 AM
From: Todd Schuman
Subject: Count based on 2 different dates

Hi Jenifer,

There is nothing special in the properties or data containers, just a list and 11.1 line chart viz. 

What version are you running?  I am on 11.1.7 FP1.  I believe you would need at least 11.1.6 to take advantage of the multiple measures on a visualization enhancement that was added.

Cognos 11.1.6: Our Favorite New Features - Senturus Blog

If you are on an earlier version and/or can't upgrade, you would have to break it up into two queries and union them together with an additional text field that described the date as "Create Date" or "Open Date".  You would then use that field to drive the color or category.

I can try to mock something up this way if you need more help.

-Todd

------------------------------
Todd Schuman

Original Message:
Sent: Mon November 02, 2020 10:30 AM
From: Jenifer Broughton
Subject: Count based on 2 different dates

Hi Todd.  This is exactly what I am looking for.  Can you share the 'properties' and the data container set up as well?  I don't see where I can add multiple fields to the values.

Thanks,
Jen​

------------------------------
Jenifer Broughton

Original Message:
Sent: Mon November 02, 2020 08:16 AM
From: Todd Schuman
Subject: Count based on 2 different dates

Hi Jenifer,

What about something like this...

Create two new items called MonthCreate and MonthClose and use the function extract to pull out the month number.  extract(Month, [CreateMonth] or [OpenDate]).  Then do a count on each to sum them up.  Count(MonthCreate or MonthClose for ID).  That will give you a consolidated count for each.  You can then add them to a chart and see the totals for each month.

I used the go sales data and product intro and discontinued date to build the sample below.

------------------------------
Todd Schuman

Original Message:
Sent: Fri October 30, 2020 08:04 AM
From: Jenifer Broughton
Subject: Count based on 2 different dates

​Just an update.

I still am unable to figure out how to get the totals under a common month.

ID                                  Create Date                  Open Date
account123                    1-1-2020                       3-1-2020
account456                    1-2-2020                       Null


So I want a graph that shows January = 2 and March = 1
I the months on each row won't necessarily be the same.  So account123 will be counted under a consolidated January and then also again under the consolidated March bucket on the axis.

------------------------------
Jenifer Broughton

Original Message:
Sent: Thu October 29, 2020 09:33 AM
From: Jenifer Broughton
Subject: Count based on 2 different dates

Hello,

Thanks Henk.  I am hoping to get this in one query and avoid a union. 

Albert - Thank you for this approach.  I'm going to give this and try.  Thankfully, I am only interested in the 'current year' so I hope your idea works.  I will update you once I try it today!

Thanks Everyone!​

------------------------------
Jenifer Broughton

Original Message:
Sent: Wed October 28, 2020 01:39 PM
From: Albert Valdez
Subject: Count based on 2 different dates

Hi Jen,
So, I tinkered around with this, you might be able to use this approach in a single query:
1. Create a calc that sums up all the account id's in a create date month: total (if([Create Date] is not NULL) then (1) else (0) for [Month])
2. Create a calc that sums up all the account id's in a open date month: total (if([Open Date] is not NULL) then (1) else (0) for [Month])
3. Sum those values and use this as your measure

This gets trickier if you are traversing multiple years, but I think you just need to add the [Year] item to your for clause "for [Year], [Month]"

------------------------------
Albert Valdez
albert@intelalytics.com

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