[Solved] Chart with Ranking of rankings

[charon]

Hi guys,

im facing the challenge to create a chart, which shows a Ranking of several former ranked data items.
For example pls imagine there are the [Productlines] 1, 2, 3....10. I have 3 Charts which show me the ranking of these 10 productlines based on their performance in three different measures.
Chart A) shows me the top 5 Productlines for Measure A,=> ProducLine 4 is Top, then PL 3, PL 8
Chart B) shows me the top 5 Productlines for Measure B => Best PL is PL 8, then second best PL is PL 7, then PL2
Chart C) ...Top 5 for Measure C => Best PL 6, 2md 8, then PL 1

Works without rankings,  just using the chart options.

BUT NOW, i want to create a chart which takes the rankings, and you can see that PL 8 is in all 3 charts in the top 3 and might be in a TOTAL RANKING based on the RANKINGs the best performer.

What ive tried so far...using 3 new Data Items with 'rank' function for the single measure charts A, B, and C. then use filters for the three "rank" to get only the top 5. THen tried to created a data item with rank for the ranks...didnt worked as expected :/

Also played a bit with the topcount function (using a DMR modell and cog 10.1.1), but the ranking of ranking..a bit tricky. Or do i not see the solution because its to trivial?  ???

thx for any ideas, cheerz :P

[MFGF]

Hi Charon,

I'm not sure I'm totally clear on what you're trying to do, but I will press on regardless... :)

Have you thought about mathematically summing the three rank values for the product lines, then dividing this by 3 to provide an average? (eg If you had a product line which was rank 1 for the first measure, rank 2 for the second and rank 3 for the third, the average would come out to be 2.) You could then rank this value to get the overall rank?

If I'm barking up the wrong tree, please forgive me :)

MF.

[CognosPaul]

It looks like you have two requirements here.

First, you want to find all of the product lines that are in all three ranking types. That can be accomplished using the intersect function:

Code Select Expand

intersect(intersect([Ranking1],[Ranking2]),[Ranking3]), with each Ranking being something like topCount([PL],10,[Measure])

The second thing is you want to order the product lines by the value of where they appear in the ranking? So if PL1 was 1,5,8 the value would be 14, and if PL2 was 5,2,1 the value would be 7 and PL2 would be first? This is very difficult. I'll need to think about it.


Hmm. Maybe something like:

Code Select Expand

bottomCount([IntersectedRanking],3,rank(tuple(Measure1,currentMember(PLHierarchy) within set pl) + rank(tuple(Measure2,currentMember(PLHierarchy) within set pl)+rank(tuple(Measure3,currentMember(PLHierarchy) within set pl))

Can't test that now, but I think it should work. It might be a bit slow, especially if you're using DMR.

[charon]

Ty guys :)

Il be in the office tomorrow and gonne try both, some functions (Paul, dude...how tf. do you do cognos for coming up with code lines like that?!=) and some logik like Muppetmaster suggested.

I let you guys know how it works...
thx and have a nice evening
cheerz

[charon]

Was not able to test it yet...our cognos system is down at the moment because some isseues caused by the migration to c10.2.

BUt another idea..could i not try to use "rank" in a tuple as measure? Tuple it with the region and create based on that a new measure which i can use for my ranking? Could this work? Maybe our cognos server will work during the weekend, gonne try it and will post the results :)
have a nice weekend
cheerz :P

[CognosPaul]

Absolutely.

Tuples only take measures and members as parameters. However, Cognos will automatically convert value expressions (such as member summaries) into measures automatically when you use it in a tuple.

It's worth noting that I expect this will be slow. Using member summaries in tuples in order to populate a set in DMR will make some extremely complex SQL.

[charon]

Hm...i see. Still, i have to try that :)

[charon]

A bit late, but just for you all to know  ::)
It worked the way suggested, means ive created a Data Item with a rank for the ranks. I also multiplied the several ranks with a small number to make sure i dont get 4xtimes rank 3 e.g.

The performance is not as bad as expected, might be the small aggregated fact table (about 120k rows).

Thx all for your great ideas and have a nice day,
cheerz :P