4 Replies Latest reply on Jan 27, 2017 10:29 AM by David Li

# LOD Roadblock - What am I missing?

Using Ver. 10.1.4

I am trying to compare a 12 period avg with the avg of the latest or most recent period using an LOD calc. The overall avg is 3.47 and the avg for the most recent period is 3.0

However in the LOD calc, which i've played with considerably, I can't seem to zero in on the the right avg. and get the same result as the overall avg.

What am I doing wrong?  Thanks you all.

Robert S.

• ###### 1. Re: LOD Roadblock - What am I missing?

Hi Robert! Try changing your Latest Period Avg to this:

{ AVG(IIF([Period] = {MAX([Period])}, [Rating], NULL)) }

EDIT: Updated the formula.

• ###### 2. Re: LOD Roadblock - What am I missing?

You're welcome! If that's the solution you want, could you please mark it as the correct answer so it doesn't show up as unanswered?

You could potentially use table calculations to get the same result, but I think it's much cleaner and easier in LOD calcs.

• ###### 3. Re: LOD Roadblock - What am I missing?

Just a side note:

In my example packaged workbook I listed only one question.  In my real life workbook I have many question.

In David's solution:

{ AVG(IIF([Period] = {MAX([Period])}, [Rating], NULL)) }

The bookend brackets {} will provide the same last Period avg for all questions - I assume it treats it like FIXED in LOD expression.  However removing the outer brackets includes the question partition, returning the last period avg for each question.

Best,

RS

• ###### 4. Re: LOD Roadblock - What am I missing?

Yes, that's right. FIXED is the default kind of LOD dimensionality, so if you have curly brackets without a keyword, it'll default to FIXED across all records in the context. If you need to do any filtering and want that filtering to affect this average, your filters must be context filters.