Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

TopN functionality in OAC not giving appropriate results

Received Response
201
Views
13
Comments

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

 

«1

Answers

  • Aman Jain-Oracle
    Aman Jain-Oracle Rank 5 - Community Champion

    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
    Chethana Kumari-Oracle Rank 6 - Analytics Lead

    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
    Chethana Kumari-Oracle Rank 6 - Analytics Lead

    Deleting the image

  • FPonte
    FPonte Rank 6 - Analytics Lead

    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
    Chethana Kumari-Oracle Rank 6 - Analytics Lead

    Thank you Fernando.

    Regards,

    Chethana

  • Chethana Kumari-Oracle
    Chethana Kumari-Oracle Rank 6 - Analytics Lead

    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
    FPonte Rank 6 - Analytics Lead

    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

    DemoSampleChethana.JPG

  • Chethana Kumari-Oracle
    Chethana Kumari-Oracle Rank 6 - Analytics Lead

    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
    FPonte Rank 6 - Analytics Lead

    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
    Chethana Kumari-Oracle Rank 6 - Analytics Lead

    Deleting.