Oracle Business Intelligence

Products Banner

Calculating Ago Metric From Fact and Dimension derived column

Received Response
1
Views
1
Comments

Hello All,

I have two tables. Table A is a dimension tables although it has Metrics in it (Aggregated table). It is Joined to Fact Table B on key1, key2, key3. Then a logical column is created using columns from two tables. A case statement is written in such a way that it will calculate "metric XX"  as  (sum(quantity)/Metric1) or (sum(quantity)/Metric2) or (sum(quantity)/Metric3). Another time dimension is joined to Fact table B. Using that "Metric XX previous month" (Ago("Business Model"."Table B"."Metric XX" ,  "Business model"."Time Dimension"."Month" , 1).

when Month from time dimension and the Metric XX from table B is pulled Into the analysis it's working fine but when Metric XX previous month is added the results are not as expected. They are duplicating as in the pic below. Please take a look and let me know if you know why it is not returning the correct values.

PS: I calculated the Metric XX previous month in the analysis then also it's not working. I tried calculating the previous month calculation using ago for Sum(Quantity) column, it's working fine. So I'm thinking the metric XX is not returning the Expected values because Table A is not joined with time dimension. Correct me if I'm wrong.

pastedImage_3.png

Table A:

Key1Key2Key3Metric1Metric2Metric3
123AAAA123
234BBBB234
345CCCC345

Table B:

Key1Key2Key3Sum (Quantity)
123AAAA10
234BBBB20
345CCCC

30

Answers

  • Robert Angel
    Robert Angel ✭✭✭✭✭

    Hi,

    can you provide a picture showing your physical and logical table joins.

    Also can you explain if your measure then you are putting time series on is physical or logical and ditto for the elements involved in its calculation.

    Finally can you show what the content levels are set to for the logical table sources involved.