Is it possible to use SUM on a column where COUNT(DISTINCT(column) ) is used?

Aloka Bhattacharya

Summary

Is it possible to use SUM on a column where COUNT(DISTINCT(column) ) is used?

Content

Hi,

Is it possible to use SUM on a column where COUNT(DISTINCT(column) ) is used?

I don't see the summation icon in Layout tab for these columns. Is there anyway to get a sum for these columns where I have used a COUNT(DISTINCT) on an OTBI Report?

Thanks,

Aloks

Aloka Bhattacharya

Update: This particular column uses a DFF. Seemingly we can't use SUMMATION on  DFFs.

We have tried CAST as well but it didn't help.

Thanks,

Aloka

Wade Wilson

Hi - are you using regular table or a pivot table? I recall having similar issues at one time and believe I resolved it somehow with a pivot.

Aloka Bhattacharya

Its a regular table Wade.

Can you share a sample of the pivot? It would be good to learn that!

Thanks,

Aloka

Wade Wilson

Hi,

Here is a simple example with a pivot table using count on person number. You'll need to adjust the person number filters in the criteria tab. https://github.com/dbldub/oracle-hcm/raw/master/customer-connect/a9b94f4762/pivot-example.xml

In the pivot table view, I edited the table and moved the count to the 'measure' region. In the rows area, I clicked summation 'after'. Video on youtube showing the same. https://youtu.be/rCwO2T7Z-PA?t=332

 

Aloka Bhattacharya

Thanks Wade!!

Let me check that.

Aloka