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

# IF Date is within Range, Sum Weighted Average

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?

• ###### 1. Re: IF Date is within Range, Sum Weighted Average

I'm guessing [Anchor Date] is a parameter and [Date] is a dimension, so make sure you add an ATTR() every time you use [Date]

eg

IF attr([Date]) <= [Anchor Date] and

attr([Date]) > [Anchor Date] - 7

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

END

• ###### 2. Re: IF Date is within Range, Sum Weighted Average

You're correct in assuming that [Date] is a dimension and that [Anchor Date] is a parameter. However applying this only results in null values.

In the screenshot below, the column "Arrival" is a calculated function applying the weighted average cost formulas. The column is constrained by a [Date] filter capturing 1 week of data. The column "Arrival (wk)" is the one i'm trying to apply the date based if statement to calculate Weighted average cost. For testing purposes, it should match the "Arrival".

Any other suggestions?

• ###### 3. Re: IF Date is within Range, Sum Weighted Average

You'll need to supply some dummy data so we can figure out what the right answer should be