1 Reply Latest reply on Feb 17, 2016 3:19 PM by Joe Oppelt

    Calculated Measure from 3 different Data Sources

    Nagaraj Koranthota

      Hi All,

       

      I am creating a calculated field based on 3 different data sources which includes Primary data source and 2 Data Secondary Data Source.

      The logic is to add 2 Quarters of data with IF ELSE and CASE statements in calculated measure .

       

      Calculated field is created in Secondary Data Source.

       

      Logic :

      ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      CASE ATTR(<PRIMARY DATA Source>.Quarter) WHEN <1>

      THEN

           IF ATTR(<PRIMARY DATA SOURCE . DIMENSION>) = <VALUE>

                THEN SUM(<SECONDARY DATA SOURCE>.measure) + SUM(<THIRD DATA SOURCE . MEASURE>)

           END

      END

      +

      CASE ATTR(<PRIMARY DATA Source>.Quarter) WHEN <2>

      THEN

      SUM(<SECONDARY DATA SOURCE>.measure)

      END

       

      ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

       

      Requirement :

      I need to display Calculated field .

       

       

      Issue:

       

      Calculated field is displaying NULL or 0 if I just drag Calculated field into the sheet.

      However if I drag Quarter Dimension into the Sheet along with Calculated measure I can see value but the value will be displayed for each Quarter.

      I need to display the calcuated field which is SUM of 2 Quarters of data.

       

       

       

      Regards,

      Nagaraj

       

        • 1. Re: Calculated Measure from 3 different Data Sources
          Joe Oppelt

          I would really have to play with it to test out stuff, but you might want to create another calc in the primary data source that has 4 values -- 1,2,3,4 -- based on the quarter, and see if using that would help.  (Use the DATEPART() function to grab quarter.)  Maybe the problem is that quarter is part of a date hierarchy, and that might be what's messing you up.

           

          I have used multiple fields from multiple secondary data sources in calcs, so that's not the problem here.

           

          If you want to hack up a sample workbook that we can play with together, consider simulating your data in an excel file with three sheets.  Create enough rows to simulate the behavior you are looking to achieve.