OBIEE Playing with TopN part 3 the Rank Function

The TOPN function is basically the RANK function with a filter. Knowing this can help us to get around the following error message:

You can translate TOPN(<<column>>,10) to a Where RANK(<<column>>) <=10.

How BottmN(<<column>>,10)?.

Try this: COUNT(<<column>>) – RANK(<<column>>) <=9 {0 based}.

So the BottomN 10 customers become:

SELECT "D0 Time"."T05 Per Name Year" saw_0, "D1 Customer"."C0  Cust Key" saw_1, "D1 Customer"."C1  Cust Name" saw_2, "F1 Revenue"."1-01  Revenue  (Sum All)" saw_3, COUNT("F1 Revenue"."1-01  Revenue  (Sum All)" by "D0 Time"."T05 Per Name Year")-RANK("F1 Revenue"."1-01  Revenue  (Sum All)" by "D0 Time"."T05 Per Name Year") saw_4 FROM "Sample Sales" WHERE COUNT("F1 Revenue"."1-01  Revenue  (Sum All)" by "D0 Time"."T05 Per Name Year")-RANK("F1 Revenue"."1-01  Revenue  (Sum All)" by "D0 Time"."T05 Per Name Year") <= 9 ORDER BY saw_0, saw_4

Till Next Time