Suppose we have the following columns:
X Y Z
Category Date Amount
A January 10
A February 20
A March 30
B January 34
B February 45
B March 65
C January 87
C February 98
C March 100
D January 80
D February 90
I want to sum the Amount column by Category and Date . So for Category A, we would have the sum of the amount be 10+20+30 = 60 for the dates between January and March. In Oracle BI, how would we do this? Note that Some categories might have missing dates. So I want to sum the Amounts for the only the the available dates between January and March. Category D, for example, has March missing. So the total amount would be 80+90 = 170.
When I do the following, I just get the sum of all the amounts:
sum("Z"."Amount")
Answer
If the required result has to be achieved through OBIEE Answer, then it can be done in following way. Create a table with columns - Category, Date, Amount. Go to Results tab. Edit view of the table.
Click on Total By icon above Category column. Both After and Report-Based Total (when applicable) should be ticked.
The result will be coming as shown.
Category Date Amount
A January 10
February 20
March 30
A Total 60
B January 34
February 45
March 65
B Total 144
C January 87
February 98
March 100
C Total 285
D January 80
February 90
D Total 170