3 Replies Latest reply on Dec 6, 2017 12:36 PM by Chris McClellan

    IF Date is within Range, Sum Weighted Average

    Ryan Lethbridge

      I've been going crazy trying to solve this issue. I'm trying to have a table that has the weighted average of cost observations for a week right next to the weight average of cost observations for the year. I've created a calculated field that sets the aggregation of weight average relative to an Anchor Date Parameter. I've tried using a standard weighted average field at the end of the if statement but it returns the aggregation error. I've also tried using and LOD expression and received no luck.

       

      e.g

      Cost (Week) =

      IF [Date] <= [Anchor Date] and

      [Date] > [Anchor Date] - 7

      then sum([Value]*[Cost])/SUM([Value])

      END

       

      or

       

      Cost (Week) =

      IF [Date] <= [Anchor Date] and

      [Date] > [Anchor Date] - 7

      then {fixed [Date] : SUM([Value]*[Cost])/SUM([Value])}

      END

       

      Any suggestions?