Division calculation is not working.

User_CEU9I

Hi all,

I have a requirement in the following way.

I have year and month as dashboard prompt filters.

My report have the following columns (I have selected year 2016 as my filter getting the following data)

Transportername          Measure column (count)     Total column     measure column/total column.

T1                                        2                                        15                         0.0

T2                                        8                                        15                         0.0

T3                                        5                                         15                         0.0

Measure column (count)

===================

count(Distinct "- Shipment Dimensions"."Shipment GID")

shipment gid is a dimension column and it has duplicates so using distinct. I need distinct count.

Total column

==================

sum(count(Distinct "- Shipment Dimensions"."Shipment GID" by "- Shipment Service Provider Dimensions"."Transportername","- Shipment Period Dimensions"."Year") by "- Shipment Period Dimensions"."Year")

Here i need sum of all 3 transporters (selected 2016 as  year in db prompt).

In the third column i am expecting result: 2nd column/3rd column

for eg: 2/15;8/15;5/15

When i try 2nd column code/3rd column code then its giving 0.0 for all the rows.

How to solve this? Any help most appreciated.

Regards,

Ram.

Gianni Ceresa

Welcome to the world of IT ....

Integer division, that's what you are seeing, so 0 is the right result.

You must force the division to use a double type doing a cast of at least one of the element of the division.

(OBIEE - Integer Datatype [Gerardnico] )

You can also use a pivot view and duplicating the column in the view you select "display as %" and OBIEE make the job itself without any calculation.

[Deleted User]

Gianni beat me to it but also:

INT? Hmmmm...already has issues with the fact that it's actually a 32bit SIGNED INT?

Robert Angel

Also beware of physical versus logic based measures, logical aggregate first and results for multiplication and division can come out wrong for this reason also.

[Deleted User]

Aka "slide #10 and #15 - pre-agg / post-agg respectively"

50 Shades of Fail KScope16

User_CEU9I

Hi all,

Thank you so much for the reply and the solution.

I should be very careful while developing. I could not notice about data types, blindly divided and unable to get the result.

I will be more focused on while developing.

Regards,

Ram.