Oracle Communities

Oracle Analytics and AI

Child Item

TopN functionality in OAC not giving appropriate results

Chethana Kumari-Oracle

Content

Hi,

TopN functionality in OAC is not giving appropriate results. Does any one know how to fix this?

My Top10LoginCount is a custom calculation that is calculated as TOPN(“Login Count”, 10).

In the screenshot attached, on the right hand side legend only 3 customers are shown.
In case, I change the calculation to TOPN(“Login Count”, 100) and specify 1 to 25 (or so) when asked for range, I can see more customers (upto 10). This means, my requirement is served by using TOPN(“Login Count”, 100).

Here my query is "why does TOPN(“Login Count”, 10) does not work as desired?"

It should ideally show me 10 customers. Does any one know how to fix this?

Thanks,

Chethana

Find more posts tagged with

Accepted answers

All comments

Aman Jain-Oracle

Hello Chethana,

There is a filter type available in OAC to get the TopN or BottomN results, can you check using that once and see what output do you get?

Attaching a screenshot for your reference.

Best Regards,

Aman Jain

Chethana Kumari-Oracle

Thanks Aman. This has helped and I am able to get Top10 customers now for a particular datacenter.

Attaching screenshot when selected 'IAD' datacenter (it shows Top10 customers for IAD) - screenshot1. Also attaching screenshot when selected 'FRA' datacenter (it shows Top10 customers for FRA) - screenshot2. This is working as per my requirement.

However, when I choose both these datacenters together in the filter, i.e., 'IAD' and 'FRA', it does not show me top10 customers for each of the datacenter (as I am expecting). Instead it shows Top10 customers in both the datacenters put together - screenshot3

Is there a way to show screenshot3 as screenshot1 and 2 together. When more than one datacenter is chosen, I want to show Top10 Customers in each of the datacenter. How do I achieve this?

Thanks in advance,

Chethana

Chethana Kumari-Oracle

Deleting the image

FPonte

Hi Chethana.

I believe This Video explains how you can achieve that.

Oracle DV - Calculate and Visualize Top N Values vs Rest of all Members

https://www.youtube.com/watch?v=HNrOLmEHgpQ

Cheers

Fernando

Chethana Kumari-Oracle

Thank you Fernando.

Regards,

Chethana

Chethana Kumari-Oracle

Hi All,

The above video link provided by Fernando is very useful. However, this does not really suiting my requirement. I am able to achieve, "TopN customers (based on login count) in each of the 10 regions". However, I am not able to achieve "Top10 customers (based on login count) in each each of 10 regions". Attached is the screenshot of what I am able to achieve.

Please let me know, what I am missing here, so that, "I could replace TopN with Top10 customers in each of the 10 regions" in the visual attached.

Screenshot 2020-02-09 at 10.53.08 AM.png

FPonte

Hi Chethana.

Try this formula to get the RANK and then apply a filter on values less than or equal to 10.

RANK(IFNULL(USAGE_COUNT,0) by DATACENTER)

It may not give you the perfect results but I think it will be close.

Cheers

Fernando

Chethana Kumari-Oracle

Thanks a lot Fernando. This is really close. It shows Top10 Customers as per my requirement.

However, I have a different problem here. I need to filter out customer names who have the word "Oracle" in their name. I have added an expression filter for this called "Non-Oracle-Customers" to do the same.

When I have this expression filter, along with the Rank calculation i.e., RANK(IFNULL(USAGE_COUNT,0) by DATACENTER), it does not give me 10 ranks, instead, it gives me only 5 for IAD region, for example (its because it fetches 10 ranks and then applies "Non-Oracle-Customers" on it). Is there a way to apply the "Non-Oracle-Customers" first and still be able to fetch 10 ranks (10 stacks for IAD in this case).

Attaching current screenshot.

Thanks in advance,

Chethana

Screenshot 2020-02-11 at 7.15.46 PM.png

FPonte

Hi Chethana.

Do you need the Oracle Customers in your report?

Why not remove them from the DataSet at the beginning in the Data Preparation?

Cheers

Fernando

Chethana Kumari-Oracle

Deleting.