Complex Distinct Sum

Question

Hi guys.

I have a table with the following fields/values:

SK_TIMESK_CAMPUSSK_CLASSSK_QUESTIONQTT_NOTE1QTT_ANSWERSID_ANSWERS_CLASS20170101004034012191934020170101004034021191934020170101004034036191934020170101004034045191934020170101004012351713131235201701010040123528131312352017010100401235351313123520170101004029916992992017010100402992299299201701010040299379929920170101004022322131717223222017010100402232225171722322201701010040223223817172232220170101004019203418819203201701010040196815104419681

* 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%7040222,8%7040337,1%704048,6%704051,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

3473493 · Answer

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

Robert Angel · Answer

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.