If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Establish Range for Days of Activities Completed.

Started by HelloCognos, 12 Jul 2018 06:51:13 AM

Previous topic - Next topic

HelloCognos

Hi,
Please see attached.  I need to show a range of values based on specific dates for the chart. So basically the ranges showing
as 1-10, 11-20, etc are ranges that are based on the total days certain activities occurred. So if the activity occurred 2 days, I need
to show it as a part of the 1-10 days bar.

I'm thinking a Case Statement so something like this,
case
when day_duration > 1 and <= 10 then '1-10'
when day duration > 11 and <= 20 then '11-20'
else
(day_duration)
end

and then just drop the Data Item for the X-AXIS value.

Let me know please if you have any suggestions.

Thanks a lot


Lynn

Quote from: HelloCognos on 12 Jul 2018 06:51:13 AM
Hi,
Please see attached.  I need to show a range of values based on specific dates for the chart. So basically the ranges showing
as 1-10, 11-20, etc are ranges that are based on the total days certain activities occurred. So if the activity occurred 2 days, I need
to show it as a part of the 1-10 days bar.

I'm thinking a Case Statement so something like this,
case
when day_duration > 1 and <= 10 then '1-10'
when day duration > 11 and <= 20 then '11-20'
else
(day_duration)
end

and then just drop the Data Item for the X-AXIS value.

Let me know please if you have any suggestions.

Thanks a lot

I think you are on the right track. A case statement must always have each possible outcome resolve to the same data type. Each of your "then" clauses are character strings so the "else" must also be a string such as '300+'.

You will also need a count metric of some sort. The item that determines the categories will go on the x-axis as you mentioned, but you'll need a measure. If your data source already has a count metric then that would seem to be the right thing to use.

You might also want to create another data item to use as a sort key. If the categories you create do not sort alphabetically in a logical way then a separate sort key data item can solve this. It would be the same expression as your case statement except the "then" clause would simply be a number to control the sorting.

Hope this helps.

HelloCognos

Thank you Lynn as always. yeap! I have the Measure and of course, the sort key sounds great as well to sort correctly.  :)