Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

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

Complex Distinct Sum

Received Response
2
Views
2
Comments
Luiz Araujo
Luiz Araujo Rank 1 - Community Starter
edited Aug 13, 2024 5:51PM in Oracle Analytics Cloud and Server

Hi guys.

I have a table with the following fields/values:

SK_TIMESK_CAMPUSSK_CLASSSK_QUESTIONQTT_NOTE1QTT_ANSWERSID_ANSWERS_CLASS
201701010040340121919340
201701010040340211919340
201701010040340361919340
201701010040340451919340
20170101004012351713131235
20170101004012352813131235
20170101004012353513131235
2017010100402991699299
2017010100402992299299
2017010100402993799299
2017010100402232213171722322
2017010100402232225171722322
2017010100402232238171722322
20170101004019203418819203
201701010040196815104419681

* Note that ID_ANSWERS_CLASS is a join of QTT_ANSWERS + SK_CLASS

And I need to do a analysis that returns something like this:

SK_CAMPUSSK_QUESTION% OF NOTE TOTAL_ANSWERS
40125,7%70
40222,8%70
40337,1%70
4048,6%70
4051,4%70

Where TOTAL_ANSWERS is:

SUM of ALL DISTINCT QTT_ANSWERS by ID_ANSWERS_CLASS.

And % OF NOTE is:

(SUM of  all QTT_NOTE1  by SK_QUESTION * 100) / TOTAL_ANSWERS.

Any clue of how to solve this problem?

Regards.

Luiz Araujo

Answers

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    Does sum(distinct qtt_answers by ID_ANSWERS_CLASS) not work for you as the basis of the first calculation?

    And

    sum(qtt_note1 by sk_question) / sum(distinct qtt_answers by ID_ANSWERS_CLASS)

    For the second.

    Note also, you may have to include the elements you are "summing by" in answers, just exclude / hide them in the presentation.

    If this does not work then I would try pushing the calcs into the rpd for the sum distinct element, as with OBIEE the tricky part tends to be working around the fact that it tries to aggregate as early as possible, and this can sometime yield spurious results, though I have used parallel logic to what you describe to create dynamic population based share of market / share of segment style functionality which is semantically identical to what you are trying to do.

  • 3473493
    3473493 Rank 3 - Community Apprentice

    Hi Luiz,

    I need one help here...in your given table, how to do the calculation in DV to get the column ID_ANSWERS_CLASS.?? Thank you