1 Reply Latest reply on Jul 7, 2015 1:11 PM by orawin2

    Grand Total on LAST() Aggregation Based on Date Dimension

    orawin2
    orawin2 Jul 7, 2015 1:09 PM

      The Grand Total is not calculating correct on Amount metric that is set to LAST() aggregation Based on Date dimension.

      When the report is run with Quarter in the criteria, the Grand Total is being calculated for the data available in the LAST month. Instead I'd assume it should calculate the Grand Total on all LAST() data available in the Quarter.

      Following are the details.

       

      Version: 11.1.1.7.140527 (Build 140515.1014 64-bit)

      Raw data:

      Grain of the underlying fact table is Amounts per month per item. The following data does not have amount for item 838 in Month 2015 / 03.

      Image1.png

       

      Requirement:

      The Requirement is to always show LAST() value for the Amount metric. The Grand Total should show the Sum of Last values.

       

      Build:

      Image2.png

      Image3.png

       

       

      Result Analysis

      Image4.png

      Shouldn't the Total for Quarter be 9,546?

       

      Issue investigation:

      BI is showing 6,502 as the Total for the Quarter.It is taking the data from last month, summing that data and displaying as the Total for the Quarter. You can see 6,502 being the total for 2015 / 03 in the following screenshot:

      Image5.png

      Is this expected behavior? I was hoping that it takes the last available data of each Item in the entire Quarter, sum it and display as the Total for the Quarter. That would be item 403 and 404 from 2015 / 03 and 838 from 2015 / 02 = 3,202 + 3,300 + 3,044 = 9,546.

      Any thoughts?

      Thank you for your time!

       

      Message was edited by: nk k

       

      Added more description to explain the intended behavior. Message was edited by: nk k