2 Replies Latest reply on Feb 14, 2016 4:21 PM by Brenda Fosmire

    Reference line - at the cell level - different each month based on calc

    Brenda Fosmire

      Employees have goal numbers based on their percent FTE times that month's base target -

      See below that Clark Rev Total Goal numbers change between Nov and Dec.

      cds monthly goal.bmp

      I want to write a calc which will set reference line at the month level..

      As an example - look at the columns with the cell level reference lines. BUT they are the same for each month.

      cds monthly goal.png

       

      Note that I've data blended.

       

      1) First I thought I needed a LOD calc to hold the calc to the month level but I can't get this to work:

      {FIXED ATTR([CDI-Monthly-All Extract].[Disch Date]): [Rev Tot Goal] * [Productive FTE]}

      --- gives the error:  Only column names are permitted in the dimensional declaration

       

      2) Then I though maybe a  more simple calc would do this:

      If MONTH(ATTR([CDI-Monthly-All Extract].[Disch Date])) = MONTH([Standards Month])

      THEN

      TOTAL([Qry Tot Goal] * [Productive FTE])

      ELSE 0

      END

       

      "If the Months match then do the calc "

      --- gives the error:

      Cannot mix aggregate and non aggregate

       

      I think I am not correctly using the MONTH function.

      I am trying to compare two dates to see if their months match?

       

      Suggestions on how to get a reference value at the month level?

      Is this a Data Blending problem?  The months I want to compare are in two different tables - but I've related them.