Incorrect Grand Total on derived fields

Question

Hi All,

We are having issue with the Grand Total of a derived field. below are the details.

We are required to derive average daily number of orders based on the logic: Sum(Fact.number of order)/ (Time.number of days for the selected period)

Scenario 1

We have daily snapshots of data. When we display Grand Total, it displays on 83464 and does not divide by the total of no days.

Scenario 2: Monthly. The orders for each month are displayed right (total orders/no of days for that month) but the Grand total displays just Sum.

We are looking for a fix to be implemented at RPD level as this will be exposed for self-service and the users cannot change the Aggregation to server complex at report level.

Version: OBIEE 11.1.1.9

Thanks..

Robert Angel · Answer

Sure, I can only hope to point to the moon, he has to raise his sight to see it....

[Deleted User] · Answer

The precise approach and its pre-agg / post-agg implications and distinctions may get lost in translation there...

Robert Angel · Answer

okay; -

Create a physical measure called Monthly Order Average. Use logic as below to assign values based on their number of days. Set its aggregation to SUM.

pseudo logic

number of orders / case when order month has 30 days then divisor = 1/30

case when order month has 31 days then divisor = 1/31

case when order month has 28 days and not leap year = 1/28

else 1/29 end

Make sure its detail is at day level in your time hierarchy.