Set Analysis Question

[darrellbutler]

Hi, I'm trying to compare monthly price movements to the price at the end of the previous fin year (Mar 2011). I have the following expression

=

avg({1<[Market] = {"Cheese"}, [Company] -= {"First*"}>}PPL)
-avg({1<[Market] = {"Cheese"}, [Month] = {"$(=min(Month))"}, [Company] -= {"First*"}>}PPL)

I want to display this against a dimension of Month so that Apr-2011 is compared against Mar 2011, May 2011 is compared against Mar -2011 and so on. Unfortunately it only works for March 2011 has anybody got any thoughts on how I can do this.Rgds Darell

[#2 · Accepted solution]

Since your dimension is month and you want to calculate for a month that is not the dimension, you need the total qualifier. I think you want:

avg({1<[Market] = {"Cheese"}, [Company] -= {"First*"}>}PPL)
-avg(Total {1<[Market]={"Cheese"},[Month]={"$(=min(Month))"},[Company]-={"First*"}>}PPL)

[darrellbutler]

Excellent - works perfectly, another one for the knowledge bank. Many Thanks!