Set analysis expression question

[#1]

Hi Everybody

I have a question about how to expression the following metrics through qlikview set analysis expression.

I want to get the formula to get:

total of value for all of the year dimension excluding the current year selection together.

For example, 2014 is selected in the current selection,  Ontario is selected in the province selection.

We need to get the total of sales for all the years excluding 2014 together with Ontario.

One thing to note is that in the year dimension, there are some records whose year value is null.

for example, fact table like below

2014 ,Ontario, 100

2015, Ontario, 1000

null(), Ontario, 500

2015, Quebec, 200

I would like to see the result to be 1300.

Thanks.

[#2]

maxgro

Thank you for the answer. I works for the case.

while if there are multiple dimensions there, I have to specify everyone of

them in the expression like below:

*sum({1<reg=P(reg),dim1=p(dim1>,dim2=p(dim2),dim3=p(dim3),dim4=p(dim4)> -

$<year=P(year)>} val)*

*Is there any way that I don't need to specify the dimensions which are in

sync with user selection?*

Thanks.

[PrashantSangle]

Hi,

Try this in text object.

=sum({<Year=>}TOTAL Sales)-sum({<Year={"=$(=Year)"}>}Sales)

Regards

[#4]

Hi Max

the aggregation I need is COUNT instead of SUM.

so the way you mentioned won't work.

Any idea about how to support count ?

Thanks.

On Thu, Nov 12, 2015 at 11:33 PM, max dreamer <qcwebmaster@qlikview.com>

[Anonymous]

Then replace sum() with count()

and Total Sales and Sales with your count measure