Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys
I am trying to ranking the number cross different hierarchies.
I build a test application
here is my script.
(lvl1-->lvl2-->lvl3-->ALL).
AA:
LOAD * INLINE [
lvl1, lvl2, lvl3, number
s1, r1, d1, 100
s2, r1, d1, 101
s3, r1, d1, 102
s4, r2, d1, 103
s5, r2, d1, 104
s6, r3, d1, 105
s7, r3, d1, 106
s8, r4, d2, 107
s9, r4, d2, 108
s10, r4, d2, 109
s11, r5, d2, 110
s12, r5, d2, 111
s13, r5, d2, 112
];
Then I will rank the number cross the 3 levels.
Ranking in lvl2
=Aggr(Rank(Sum({<lvl2=,lvl1=>} number)),lvl2,lvl1)
Ranking in lvl3
=Aggr(Rank(Sum({<lvl2=,lvl1=>} number)),lvl3,lvl1)
Ranking in ALL
=Aggr(Rank(Sum({<lvl2=,lvl1=>} number)),lvl1)
I use set analysis in the expression to allow user select any level fields and wont impact the result.
Please see the attached qvw file.
Everything above are working perfectly. My problem is: when I use the same method in the real application.
Sometimes it give me unexpected answer? Is there anyone can explain it?
In the 'real' application. Here is the screen shot:
as you can see it total random not ranking properly.
Can you upload documents where you are getting problems.?
Sorry my friend, the documents is 10GB and it has the real company data.
I just have another test which create a empty new qvw file then do an binary load from the original QVW file
Then I copy the rank object over and it works very well in the new qvw file.
I think there is northing wrong with "Rank" function.
People who see this thread can use this rank example safely.
I will do few more test and update the results.
Thanks
Hi,
Use all the 3 levels in Set analysis will do.
=Aggr(Rank(Sum({<lvl2=,lvl1=,Lvl3=>} number)),lvl2,lvl1)
BR,
Chinna
Thanks Chinna
I tried, but not working.
I think if we not select the Lvl3, it won't do.
Thanks anyway.
Cheers
Interesting. I got this issue fixed by add a time in the set analysis. and in fact it not change the total figures but reorder the rank. I can't understand it. It is just works.
Ranking in lvl2
=Aggr(Rank(Sum({<lvl2=,lvl1=,time=>} number)),lvl2,lvl1)