reverse rank in Cognos

[Maven4Champ]

I am doing a report of the top 15 and bottom 15 dealers in our region. Currently, I have a rank column that assigns the rank based on value of another column (usage). I want to do a second column that reverses the rank (assigning #1 to the dealer with the lowest usage isntead of the highest). So we can see a top 15 most-utilized and a top 15 most un-utilized...

Is this possible?

 

[flex13]

What product are you using?

 

[Maven4Champ]

Cognos Impromptu version 7.1.529.0
- as well I forgot to mention, I would gladly do this in a sub-report (even though it might be a little tougher) if that is easier. The problem is getting the second rank column to assign the #1 rank and so on, to the lowest ranking dealers. I just can't seem to reverse the functionality, and I will tell you what - using the actual "reverse" function did nothing! LOL

 

[GJParker]

One way to do this would be to use a formula with a prompt to use as your ranking column i.e.

Create a prompt with 2 options Top and Bottom. Then create a formula

//RankFormula
If ( ?RankPrompt? = 'Top' ) then ( Value ) else (value * -1)

Set this formula to be sorted descending and then create a 2nd formula to Rank "RankFormula"

This way the sort order will reverse dependimg on the prompt entry.

HTH


Gary Parker
MIS Data Analyst
Manchester, England

 

[Maven4Champ]

Ok I already have the start date and end date prompts. Are you saying I need to go to the Prompt Manager, and add two more fields. One named Top and the other named Bottom?

If so, what should their type be and data type be?

Second, where do I create/input the formula at? Are there going to be two columns after doing this?

 

[GJParker]

You need to create 1 new prompt, for this example create a type in prompt (string). (RankPrompt)

In the report query manager create the calculation in my first post (RankFormula)

Then create a 2nd calculation to Rank the RankFormula calculation.

when you run the report if the user types in "Top" the report will be sorted with your useage column Descending, if the user types anything else or is left blank the report will be sorted with the usage column Ascending.

The RankFormula column does not need to dipslay and can be hidden on the report.

HTH






Gary Parker
MIS Data Analyst
Manchester, England

 

[Maven4Champ]

That was my problem...

I couldn't figure out how to create a calcuation that states:

//RankFormula
If ( ?RankPrompt? = 'Top' ) then ( Value ) else (value * -1)


Do I do use string, then just past the above in?

As well, you have the variable "VALUE" above. I couldn't find that option in my calulcation Definition options...

I have the prompt created. Method: Type In. Name is RankFormula

I just need to actually create the calculation definition now. That is where I am having trouble with.

 

[GJParker]

Create the calculation in the Calculation Defintion editor

If (?Your Prompt Field? = 'Top') then ( your usage field ) else (your usage field * -1)



Gary Parker
MIS Data Analyst
Manchester, England

 

[Maven4Champ]

As well, a thought just came to my mind...

My original rank is based up ranking the "Count" column which is associated by dealer name. Don't I have to associate this somehow with that rank column - as of now I see no where it actually associates it with?

Gray, I can't find where to get the ( your usage field) variable at within the calcualtion window? Which category is it under?

 

[GJParker]

I have a rank column that assigns the rank based on value of another column (usage).

you have a field/column called "usage" in your report

My original rank is based up ranking the "Count" column which is associated by dealer name. Don't I have to associate this somehow with that rank column - as of now I see no where it actually associates it with?

remove the existing column from your report and replace it with a new one that ranks the new calculation.


Gary Parker
MIS Data Analyst
Manchester, England