Oracle Transactional Business Intelligence

Home› Oracle Analytics› Forums› More Solutions› Oracle Transactional Business Intelligence

Categories

Count positive values and compare with total count to get the aggregated share of positive values

Received Response
84
Views
3
Comments
Stefan pettersson
Stefan pettersson ✭
in Oracle Transactional Business Intelligence

Summary:

How can I count the number of positive values in a column and compare with the total count to get the share percentage of positive values?

Content (required):

I have column with positive, negative and 0 values. I want to count the number of positive values (>0) and then compare with the count of all values to show which percentage of the values are positive on an aggregated level.

Please see screenshot for details.

Thanks in advance!

Version (include the version you are using, if applicable):

22D

Code Snippet (add any code snippets that support your topic, if applicable):


Tagged:

Answers

  • Amrita Gupta-Oracle
    Amrita Gupta-Oracle ✭✭✭✭✭

    Hi @Stefan pettersson,

    I used the below formulae, and, the have included screenshot of the results as well. Please check if this works for you -

    Positives ---- SUM(CASE WHEN <"Fact"."Col"> > 0 THEN 1 ELSE 0 END)

    Total ------ COUNT("Fact"."Col")


  • Venkat Koduvayur-Oracle
    Venkat Koduvayur-Oracle ✭✭✭

    Hi @Stefan pettersson

    You could define the formula as below

    No of Positive Values: SUM(CASE WHEN "Facts"."Amount">0 THEN 1 ELSE 0 END)

    Count of All Values:  count("Facts"."Amount")

    % of Positive Values: (SUM(CASE WHEN "Facts"."Amount">0 THEN 1 ELSE 0 END)/count("Facts"."Amount"))*100


    Have used both table view and pivot view to aggregate the data at business unit level.


    Pivot view setting



    Hope this helps


    Regards

    Venkat

  • Stefan pettersson
    Stefan pettersson ✭

    Thanks Amrita and Venkat!

    Appreciate the added info Venkat, very useful! Thanks