Adding Date check in Query Expression stops data from displaying

[willg]

Hi All,

I am currently having a frustrating problem where I need to display Open SR's & Closed SR's on a chart. All works fine when I just do the query expression based on status (when status = 'NEW', or when status = 'CLOSED' for example).  As shown below.



However, doing the queryn in this way meant that cognos was duplicating records and counting them twice. The only way I've found to stop this from happening is by adding in a second check as follows.

"status = 'NEW' AND extract(month,[changedate]) = extract(month,[reportdate])"

Adding this second part causes the query to return the right values, however for some reason stops it from displaying on the chart. As shown below.



However I know the data is right, since I displayed in just a singleton query. I suspect it's clearly a problem with the date(s) but not sure how I can solve it, while still keeping the date check in the Query Expression. If anyone has any insight into this problem it would be greatly appreaciated. I might not be able to access Cognos again until tomorrow morning however.

Thanks,
Will. 

[MFGF]

Hi,

When you refer to "adding in a second check" can you elaborate? Are you doing this in a query calc or in a filter? If a calc, can you tell us the entire syntax you are using?

Cheers!

MF.

[willg]

OK Sorry about the confusion.

My original Query Calc (the working one) is as follows:

"case when [status] NOT IN ('RESOLVED','CLOSED') then [ticketid] else null end"

Then the second Query Calc I made, which I was referring to as adding a 'second check' is as follows:

"case when [status] NOT IN ('RESOLVED','CLOSED') AND extract(month,[changedate]) = extract(month,[reportdate])  then [ticketid] else null end"


There is a Count Distinct aggregate function applied to the data item, as I do not want to count the same [ticketid] more than once. I've also just noticed the error in the screenshot does not happen when a Count aggregate function is applied, only with Count Distinct.

Hopefully this gives you some more insight!

Regards,
Will