Set Analysis without selection

[#1]

Hi all,

I have a problem with my app.

I have one dimension :

- Country

And 3 expression :

- Turnover N : TO

- Turnover N-1 : TON

- Common Turnover N-1

In the "Common Turnover N-1", I want to have the Turnover N-1 Only for products wich have Turnover in N and N-1.

So I use this expression :

sum ( aggr( if ( sum(TO) > 0 AND sum({<YEAR={$(vMaxYearN1)}>}TO) > 0 , sum({<YEAR={$(vMaxYearN1)}>}TO) , 0 ) , Product))

When I have all dimension, the result is good.

But when I clik on one country, the result change.....because the expression work on common article for just this country.

But I want the expression work on all product, with or without selection, To have the same result...

I'm not sure I explain correctly, sorry for my english...

Thanks in advance

Nicolas

[swuehl]

You can clear selections in a field using set analysis, e.g. to clear selections in Country:

sum ({<Country= >} aggr( if ( sum({<Country= >} TO) > 0 AND sum({<YEAR={$(vMaxYearN1)}, Country= >}TO) > 0 , sum({<YEAR={$(vMaxYearN1)}, Country= >}TO) , 0 ) , Product))

Not sure if this is what you want or what you need, so it would be helpful if you could explain your data model a bit more detailed, best by uploading a small sample QVW.

[#3]

Thanks for that, but it's not what I need.

I give you a small sample QVW.

The column CA N is the Turn Over

The column Top CA is just a TOP column with "1" if the product has turnover, and "0" if not.

The dimension is a group cycle, with :

- Région (Country)

- Organisation (Company)

- Marque (Brand)

- Gamme (Product Family)

When I clik on a Région or Organisation, the result of "Top" column changing...And I don't want !

Thanks in advance for that

[swuehl]

Maybe something like attached?

[#5]

Hi I just looked your sample.

It's always change....look, when you go on "Region" and click on "7", the result move from 153 to 125

[swuehl]

Not in my QV version. It returns same value 152 with or without selection in Region.

Using 11.20 SR11 X64.

Which version are you using?

[#7]

I use 11.20 SR12 x64.

Yes it's runing for Region 7 but you selected a Gamme and a Fournisseur.

If you just Select a ANNEE and a MONTH, and clik to 7 the result is wrong

[swuehl]

I think the issue is a grain mismatch between the inner dimension Article in your advanced aggregation and the chart dimension. Have a look at

Pitfalls of the Aggr function

I think what you see without selecting a Region is not the correct value.

I added the chart dimension to your aggr() dimensions, like

=sum( aggr( if (sum( CA) > 0 , 1 , 0), [$(=GetCurrentField( GRP ))] ,Code_Article))

Now the values don't change, and I think the values are correct.

[#9]

I'm really sorry but it's not good for two reason :

- If you change the dimension, and you replace the group GRP by the field Article, you have "1" in TOP column when the Turnover is negative....and you have not only "1" but sometimes 2-3-4-5-6...

I extract to Excel the table with "Article" in dimension, and I create a function in Excel, the result is 1963 for total TOP.

- If you replace in the "IF" the "1" by "Sum(CA)" like that :

sum( aggr( if (sum( CA) > 0 , sum( CA) , 0), [$(=GetCurrentField( GRP ))] ,Code_Article))

The total of Top if 12 390 842$. But normaly the result should be minus that the colume "CA N" (12 328 606$).

Do you understand ? (I'm not sure with my english...)

[swuehl]

What do you mean with :

"If you change the dimension, and you replace the group GRP by the field Article, you have "1" in TOP column when the Turnover is negative....and you have not only "1" but sometimes 2-3-4-5-6...

I extract to Excel the table with "Article" in dimension, and I create a function in Excel, the result is 1963 for total TOP."

If you change the dimension to Article, you shouldn't just use the same expression I posted above, rather something like

=if (sum( CA) > 0 , 1 , 0)

and enable total mode 'sum-of-rows'.

And I think it's correct that you'll get a total larger than the total for CA N, because I am summing only the positive amounts, not also the negative.