Grouping calculation results in Dashboards

[Binksy]

I'm a new Cognos user so perhaps there's an easy solution for this.

First off I'm using IBM Cognos Analytics 11.0.8.

I'm building a dashboard based on data from a large database, and I want to group the result of a calculation for a chart.
The calculation is the difference between two column values, where the two column values are dates and the results are in this format "1 HH:MM:SS" (days hours:minutes:seconds).
The purpose of the calculation is to show the amount of time used to process cases.

As this is a large database, I want to group the results to something like "1-5 days, 5-9 days" and onwards.

I've tried using the calculation editor to make a filter in two ways, but the filter isn't accepted.

Example 1:
IF((( Calculation_name ) >= '0 00:00:00') AND (( Calculation_name )<='5 23:59:59'))
THEN ('1-5')
ELSE IF(((Calculation_name) >= '5 00:00:01') AND ((Calculation_name)<='10 00:00:00'))
THEN ('5-10')

Example 2:
CASE
WHEN ((Calculation_name) BETWEEN '1 00:00:00' and '5 23:59:59'
THEN '1-5'
ELSE 'More'
END

I've also tried replacing "Calculation_name" with the actual calculation.

Does anyone have a suggestion to make this work? Or maybe another solution?

[MFGF]

I'm a new Cognos user so perhaps there's an easy solution for this.

First off I'm using IBM Cognos Analytics 11.0.8.

I'm building a dashboard based on data from a large database, and I want to group the result of a calculation for a chart.
The calculation is the difference between two column values, where the two column values are dates and the results are in this format "1 HH:MM:SS" (days hours:minutes:seconds).
The purpose of the calculation is to show the amount of time used to process cases.

As this is a large database, I want to group the results to something like "1-5 days, 5-9 days" and onwards.

I've tried using the calculation editor to make a filter in two ways, but the filter isn't accepted.

Example 1:
IF((( Calculation_name ) >= '0 00:00:00') AND (( Calculation_name )<='5 23:59:59'))
THEN ('1-5')
ELSE IF(((Calculation_name) >= '5 00:00:01') AND ((Calculation_name)<='10 00:00:00'))
THEN ('5-10')

Example 2:
CASE
WHEN ((Calculation_name) BETWEEN '1 00:00:00' and '5 23:59:59'
THEN '1-5'
ELSE 'More'
END

I've also tried replacing "Calculation_name" with the actual calculation.

Does anyone have a suggestion to make this work? Or maybe another solution?

Hi,

Can you be specific about where you are trying to use this syntax? You mentioned a filter, but filters are for including or excluding rows of data, which isn't what you need here. Are you using the expression in a calculation?

What do you mean when you say "the filter isn't accepted"? Do you get an error - if so, what error? Do you get the wrong result? Something else?

Have you tried using the _days_between() function in your original calculation to return the number of days between the two dates, then using this integer result to work out your buckets?

Cheers!

MF.

[Binksy]

Sorry for not being specific enough! Also, English is a second language for me, so I'm having some issues expressing myself.
I was trying to use the syntax either as a filter or as data input for my graph. I want the data to appear grouped, but also to have the alternative to exclude some groups from the graph when wanted/needed.
I used another expression in my calculation, just a basic (DataColumn1) – (DataColumn2) which yielded results in the format I mentioned (D HH:MM:SS).

What I meant when saying "the filter isn't accepted" was that I get an error saying "Filter values can't be loaded" when trying to apply the expression as a filter and an error saying "Internal Error" when trying to apply it as data for my X-axis.

I was not aware of the days_between() function, but using it works as a charm and integers are so much easier to work with. Got the exact result I wanted.
Thank you!

[MFGF]

Sorry for not being specific enough! Also, English is a second language for me, so I'm having some issues expressing myself.
I was trying to use the syntax either as a filter or as data input for my graph. I want the data to appear grouped, but also to have the alternative to exclude some groups from the graph when wanted/needed.
I used another expression in my calculation, just a basic (DataColumn1) – (DataColumn2) which yielded results in the format I mentioned (D HH:MM:SS).

What I meant when saying "the filter isn't accepted" was that I get an error saying "Filter values can't be loaded" when trying to apply the expression as a filter and an error saying "Internal Error" when trying to apply it as data for my X-axis.

I was not aware of the days_between() function, but using it works as a charm and integers are so much easier to work with. Got the exact result I wanted.
Thank you!

That sounds like a great result! When you simply subtract two dates, the result you end up with is what's called an Interval. These are notoriously difficult to work with, so it's often the best idea to use date functions such as _days_between(), _months_between() and _years_between() to calculate the differences you need.

Cheers!

MF.