topic Set Analysis Question in QlikView

Set Analysis Question

darrellbutler — Fri, 27 May 2011 12:54:26 GMT

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

Re: Set Analysis Question

Fri, 27 May 2011 13:58:11 GMT

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)

Set Analysis Question

darrellbutler — Fri, 27 May 2011 14:03:21 GMT

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