How to get Top 10 Products out of 1000 rows of data

[Former Member]

I have a table with 1000 rows for the entire year. I have a requirement to find Top 10 Products ( top 10 ranks) per revenue per region.

When I bring all 100 rows to excel , how do I find the Top 10 products based on revenue? . Since Xcelsius cannot do any sorting to find the Top 10 what is best solution ?

I know Centigon Solutions provide a DrillFilter Addon, , bit would like to achieve this with the existing Xcelsius product.

Any recommendations?

Thanks

Pushpa

[Former Member · Accepted solution]

What is the source of your data and how is it getting into Xcelsius? My top 3 recommendations (in order) would be...

Only bring in the top 10, ranked.

Bring in as many as you want, but have a column that ranks them, straight from the database (oracle rank function is great), then do vlookups on ranks 1-10.

Bring in as many as you want, but create a formula column that ranks them, then do vlookups on ranks 1-10.

[Former Member · Accepted solution]

Thanks for the response and feedback.

My data source is sql server. I bring data in to xcelsius thro Live office connection.

Webi reports are built to fetch data from database.

I have 2 requirements.

1st one is Live dashboard which works perfectly because when region and revenue type is selected from the combo box, it refreshes data and for this I bring only top 10 rows.

2nd requirement is Static Dashboard. For this , I need to bring data for all regions and for all revenue types.

There are 5 regions and 8 revenue types which makes my data set to grow 1000 rows. For every region, for every revenue type, I have to display Top 10.

This is where I am lost.

Please advise.

Thanks

[Former Member · Accepted solution]

I would use the "Filtered Rows" Insertion type and water fall it from Regions and Revenue Types.

So, the data you would bring in would look something like this...

Region Revenue Type Dept Amount Rank

-

---

-

---

-

---

-

---

-

---

North Belts A14 100 1

North Belts B23 50 2

etc

North Belts A22 15 10

North Shirts B61 120 1

etc

North Shirts A25 14 10

South Belts A18 650 1

etc

South Belts D45 75 10

South Shirts C61 2100 1

etc

South Shirts A25 1300 10

And whatever component you use to select a region would then use the "Filtered Rows" Insertion Type to move all of the rows for that region into a subsequent area in the spreadsheet. That area would then serve as the source data for the Revenue Type selecting component, which would then pass the filtered rows into a third area, which would display the data.

[Former Member · Accepted solution]

Thanks Jamie. I tried this approach and we can accomlish using this method only if you have 4 regions.

Tables have data for North America, Latin America, EMEA, APJ , and for all RevTypes.

but I should also bring data Global. region and ALL RevType data that means Global and ALL Revtypes. combination for all revtype.

So mimicking to get Global and ALL is my problem in static dashboard.

I am thinking of wriring a PL/sql in sql server to get all region , Rervtype data ( only top 10) and with union to get Global values and load it in one table and use that table to build webi report and bring it to excel file.

Do you think this approach is better or is there any better method?

Thanks

[Former Member · Accepted solution]

There should not be a limit on the number of regions you can support. If you add a Region called Global and list the aggregated Top10 as its children, and same for Revenue Types, you should be able to do it. I actually do this all the time, it just feels weird because you are aggregating and shoving that data in with the data you are aggregating.

North Belts A14 100 1

North Belts B23 50 2

etc

North Belts A22 15 10

North AllRevTypes B61 120 1

etc

North AllRevTypes A25 14 10

Global Belts A18 650 1

etc

Global Belts D45 75 10

Global AllRevTypesC61 2100 1

etc

Global AllRevTypes A25 1300 10

[Former Member · Accepted solution]

Jamie,

can you please provide me example for my reference , then it would be easy for me to follow.

Thanks

[Former Member · Accepted solution]

I've made up a sample, not sure how to attach a file.

[Former Member · Accepted solution]

Jamie, I have e-mailed to you just now from my gmail account. Please send your example to my gmail account.

[Former Member · Accepted solution]

Just sent it.

[Former Member · Accepted solution]

Jamie,

this is exactly what I wanted .

Thank you very much .

Pushpa

[Former Member · Accepted solution]

Hi Pushpalatha,

I also have the same requirements. If you can help to send, it would be helpful to me...

Thanks