Oracle Analytics and AI

Daniel Gerçossimo

Good morning community!

In OBIEE 12c I have a report and it has the following situation:
   Count (distinct id) * value

The value is the same for all IDs in a given month and more than one monthly record can occur, but when I add the results every year the result is wrong.

For example:
  Year month registration id value
  2014 1        5                 1 10.00
  2014 1        6                 1 10.00
  2014 1        7                 2 10.00
  2014 1        8                 3 10.00
  2014 1        9                 4 10.00
  2014 1       10                1 10.00
Count (distinct id) * value = 4 * 10.00 = 40.00

  Year month registration id value
  2014 2        5                 1 10.00
  2014 2        6                 1 10.00
  2014 2        7                 2 10.00
  2014 2        8                 3 10.00
  2014 2        9                 4 10.00
  2014 2       11                5 10.00
Count (distinct id) * value = 5 * 10.00 = 50.00

When making the sum of the year must return 40.00 + 50.00 = 90.00 however it is returning 50.00

How should I do to return the correct value?

Grateful,
Daniel.

Gianni Ceresa

Daniel,

50 is the correct value if you look twice at your formula: in the total count distinct work on all the ID forgetting the month, so it just find 5 distinct IDs.

You can force the things by setting the "Aggregation Rule (Totals Row)" of your column to SUM instead of Default.