Oracle Analytics Cloud and Server

Products Banner

TopN functionality in OAC not giving appropriate results

Received Response
173
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

 

Answers

  • 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 

  • 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

  • FPonte
    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

  • Thank you Fernando.

    Regards,

    Chethana

  • 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 ✭✭✭✭✭

    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

  • 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 ✭✭✭✭✭

    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

  • FPonte
    FPonte ✭✭✭✭✭

    Hi.

    Didn't the Sort option work for you?

    Cheers

    Fernando

    SortOrder.JPG

  • Hello,

    Nope. This sort has'nt helped me. In fact, it has been sorted in terms of usage count from high to low.

    However, this is not being applied to usage count corresponding to stacks within each bar, instead this sort has been applied on usage count corresponding to individual bars (regions) as a whole, i.e., IAD has most usage count compared to PHX and so on.

    Thanks,

    Chethana

  • FPonte
    FPonte ✭✭✭✭✭

    Hi Chethana.

    Sorry for the late response.

    Not sure what is happening as it worked fine on my test.

    Cheers

    Fernando