Percentage Rank in webI

[jyothirmayee_s]

Hi Gurus,

I need to do a Percentage Rank in WebI.. just like PercentageRank() in Excel.

I have Date and WeekAgg from SQL . Report is based on Universe and did some work around on few formulas on Percent Rank but in vain.

Rank(WeekAgg)/ Runningcount(WeekAgg) and Nofilter() option also did not work.

Any help on this would be appreciated.

Thanks,

Jothi

[jyothirmayee_s · Accepted solution]

Solved the Percentage Rank..

Below is formula in case anyone had similar requirement:

Max(Rank of value) - (Rank of Value) / Max(Rank of Value) -1

Thanks,

Jothi

[Former Member]

Hi Jothi,

Try these below steps to achieve PercentRank in WebI.

My report has City and Sales Revenue from eFashion universe. now I want to achieve Percentage Rank for Sales Revenue.

Note: I have sorted the Sales Revenue by Descending order.

Let's create below variable to achieve the PercentRank in WebI report.

Step 1: RCount=RunningCount([Sales revenue])

Step 2: Numerator=(Max([RCount]) In Block)-[RCount]

Step 3: Denominator=(Max([RCount]) In Block)-1

Step 4: PercentRank=[Numerator]/[Denomiator]

Here is screen shot of WebI report:

Hope this will help..

Regards,

Mahender

[jyothirmayee_s]

thank you Mahender,

Somehow values in WebI are not matching with the PercentRank() in Excel.

Followed step by step.

Thanks,

Jothi

[Former Member]

Hello Jyothi,

Could you please post the snap shot of report where you need Ranking and also if possible sample data.

Regards

Niraj

[jyothirmayee_s]

Here is the sample data Niraj,

Week Agg	Date
1.417538	2016-01-15 0:00:00
1.4535257	2016-01-18 0:00:00
1.4843217	2016-01-19 0:00:00
1.5423534	2016-01-20 0:00:00
1.2414014	2016-01-21 0:00:00
1.3170339	2016-01-22 0:00:00
1.3214149	2016-01-25 0:00:00
1.2991232	2016-01-26 0:00:00
1.251124	2016-01-27 0:00:00
1.2611498	2016-01-28 0:00:00
1.22653	2016-01-29 0:00:00
1.215677	2016-02-01 0:00:00
1.4073745	2016-02-02 0:00:00
1.3964905	2016-02-03 0:00:00
1.2290008	2016-02-04 0:00:00
1.1941258	2016-02-05 0:00:00
1.2198236	2016-02-08 0:00:00
1.3070505	2016-02-09 0:00:00
1.54401	2016-02-10 0:00:00
1.5763657	2016-02-11 0:00:00
1.8966991	2016-02-12 0:00:00
1.6242883	2016-02-15 0:00:00
1.4737538	2016-02-16 0:00:00
4.5010989	2016-02-17 0:00:00
1.03634	2016-02-18 0:00:00
1.5256052	2016-02-19 0:00:00
1.3072981	2016-02-22 0:00:00
1.3701908	2016-02-23 0:00:00
1.3501286	2016-02-24 0:00:00
1.3525997	2016-02-25 0:00:00
1.3589448	2016-02-26 0:00:00
1.1994886	2016-02-29 0:00:00
1.1645847	2016-03-01 0:00:00
1.1833032	2016-03-02 0:00:00
1.1483049	2016-03-03 0:00:00
1.1379892	2016-03-04 0:00:00
1.2284523	2016-03-07 0:00:00
1.3034896	2016-03-08 0:00:00
1.3197164	2016-03-09 0:00:00
1.3743454	2016-03-10 0:00:00
1.237942	2016-03-11 0:00:00
1.667452	2016-03-14 0:00:00
1.1417344	2016-03-15 0:00:00
1.1380799	2016-03-16 0:00:00
1.1531916	2016-03-17 0:00:00
1.6694566	2016-03-18 0:00:00
1.3464596	2016-03-21 0:00:00
1.2367309	2016-03-22 0:00:00
1.2050511	2016-03-23 0:00:00
1.262059	2016-03-24 0:00:00
1.2711248	2016-03-25 0:00:00
1.1742762	2016-03-28 0:00:00
1.2344608	2016-03-29 0:00:00
1.3568378	2016-03-30 0:00:00
1.4579696	2016-03-31 0:00:00
1.44549	2016-04-01 0:00:00
1.2135338	2016-04-04 0:00:00
1.2115225	2016-04-05 0:00:00
1.2424854	2016-04-06 0:00:00
1.2032235	2016-04-07 0:00:00
1.2041909	2016-04-08 0:00:00
1.2576565	2016-04-11 0:00:00
1.244243	2016-04-12 0:00:00
1.2239486	2016-04-13 0:00:00
1.2447724	2016-04-14 0:00:00
1.2943211	2016-04-15 0:00:00
1.6202371	2016-04-18 0:00:00
1.5601104	2016-04-19 0:00:00
1.9351024	2016-04-20 0:00:00
1.3379297	2016-04-21 0:00:00
1.2974853	2016-04-22 0:00:00
1.1792553	2016-04-25 0:00:00
1.1910662	2016-04-26 0:00:00
1.156911	2016-04-27 0:00:00
1.162921	2016-04-28 0:00:00
1.160236	2016-04-29 0:00:00
1.1923866	2016-05-02 0:00:00
1.1968198	2016-05-03 0:00:00
1.2058057	2016-05-04 0:00:00
1.2417257	2016-05-05 0:00:00
1.2190516	2016-05-06 0:00:00
1.2053215	2016-05-09 0:00:00
1.1916949	2016-05-10 0:00:00
1.2199519	2016-05-11 0:00:00
1.2128917	2016-05-12 0:00:00
1.2459997	2016-05-13 0:00:00
1.1931329	2016-05-16 0:00:00
1.1904562	2016-05-17 0:00:00
1.1869068	2016-05-18 0:00:00
1.2544782	2016-05-19 0:00:00
1.1889124	2016-05-20 0:00:00
1.2242992	2016-05-23 0:00:00
1.3352291	2016-05-24 0:00:00
1.3764409	2016-05-25 0:00:00
1.3392145	2016-05-26 0:00:00
1.2719171	2016-05-27 0:00:00
1.2676458	2016-05-30 0:00:00
1.2208647	2016-05-31 0:00:00
1.3230921	2016-06-01 0:00:00
1.334861	2016-06-02 0:00:00
1.2764429	2016-06-03 0:00:00
1.1371984	2016-06-06 0:00:00
1.214529	2016-06-07 0:00:00
1.1751326	2016-06-08 0:00:00
1.2162213	2016-06-09 0:00:00
1.1704944	2016-06-10 0:00:00
1.3970243	2016-06-13 0:00:00
1.2971514	2016-06-14 0:00:00
1.3367769	2016-06-15 0:00:00
1.1513306	2016-06-16 0:00:00
1.249751	2016-06-17 0:00:00
1.2008938	2016-06-20 0:00:00
1.1092294	2016-06-21 0:00:00
1.1024135	2016-06-22 0:00:00
1.1267619	2016-06-23 0:00:00
1.0936993	2016-06-24 0:00:00
1.0656142	2016-06-27 0:00:00
1.1630598	2016-06-28 0:00:00
1.1789996	2016-06-29 0:00:00
1.250543	2016-06-30 0:00:00
1.2621604	2016-07-01 0:00:00
1.3851393	2016-07-04 0:00:00
1.3113551	2016-07-05 0:00:00
1.3046606	2016-07-06 0:00:00
1.2399582	2016-07-07 0:00:00
1.2132807	2016-07-08 0:00:00
1.1560036	2016-07-11 0:00:00
1.2834808	2016-07-12 0:00:00
1.2681513	2016-07-13 0:00:00
1.2534436	2016-07-14 0:00:00
1.2648472	2016-07-15 0:00:00
1.1764939	2016-07-18 0:00:00
1.2042581	2016-07-19 0:00:00
1.2859903	2016-07-20 0:00:00
1.2451402	2016-07-21 0:00:00
1.1338271	2016-07-22 0:00:00
1.2053662	2016-07-25 0:00:00
1.1404434	2016-07-26 0:00:00
1.1344831	2016-07-27 0:00:00
1.1062732	2016-07-28 0:00:00
1.2239705	2016-07-29 0:00:00
1.1142864	2016-08-01 0:00:00
1.1659098	2016-08-02 0:00:00
1.2038841	2016-08-03 0:00:00
1.1785236	2016-08-04 0:00:00
1.1372428	2016-08-05 0:00:00
1.185001	2016-08-08 0:00:00
1.1702108	2016-08-09 0:00:00
1.1593924	2016-08-10 0:00:00
1.186335	2016-08-11 0:00:00
1.2478162	2016-08-12 0:00:00
1.3440905	2016-08-15 0:00:00
1.3435463	2016-08-16 0:00:00
1.3527144	2016-08-17 0:00:00
1.3628456	2016-08-18 0:00:00
1.362757	2016-08-19 0:00:00
1.1434474	2016-08-22 0:00:00
1.2142137	2016-08-23 0:00:00
1.2242109	2016-08-24 0:00:00
1.1903698	2016-08-25 0:00:00
1.179872	2016-08-26 0:00:00
1.1534731	2016-08-29 0:00:00

[Former Member]

Jothi,

I have calculated the PercentRank for sample top 10 weekagg values.. let me know are you expecting the same values in WebI..Please refer screen shot attached below.

Note: I have sorted the Week Agg values (Largest to Smallest)

Regards.

[amitrathi239]

have you tried these ranking percentage options.