Multiply count(distinct) by sum ()

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.

nm.Mani

Hi

try something like this, by concatenating month and id

Count (distinct id || month ) * value.

Thanks

MM

Daniel Gerçossimo

I tried to force the rule with sum but there it is giving a higher value than it should!
I'll post a picture of the results!

Daniel Gerçossimo

There is a variable that alternates between months, bimester, quarter or semester so I do not know if it would work, but I'll try !!

Gianni Ceresa

I tested the formula over a year and the result was good.

Now if your case is different than what you posted of course things change ...

Daniel Gerçossimo

Grateful Gianni Ceresa really worked out I will check if you have other rules that are changing my result!

Daniel Gerçossimo

Sorry for the english, but I'm from Brazil and I'm using a translator !!

Daniel Gerçossimo

I discovered the problem ... I am using another dimension that separates the values in two situations and in this case the values can not be summed apart as in the final result!

I'll analyze better and see what I can do, but if anyone has an idea, thank you very much !!