topic Re: Set Analysis without selection in QlikView

Set Analysis without selection

Thu, 28 Jan 2016 13:58:57 GMT

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

Re: Set Analysis without selection

swuehl — Thu, 28 Jan 2016 18:17:53 GMT

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.

Re: Set Analysis without selection

Fri, 29 Jan 2016 15:26:02 GMT

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

Re: Set Analysis without selection

swuehl — Fri, 29 Jan 2016 17:52:45 GMT

Maybe something like attached?

Re: Set Analysis without selection

Mon, 01 Feb 2016 09:30:08 GMT

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

Re: Set Analysis without selection

swuehl — Mon, 01 Feb 2016 11:00:09 GMT

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?

Re: Set Analysis without selection

Mon, 01 Feb 2016 11:11:18 GMT

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

Re: Set Analysis without selection

swuehl — Mon, 01 Feb 2016 14:20:19 GMT

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.

Re: Set Analysis without selection

Mon, 01 Feb 2016 14:34:07 GMT

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...)

Re: Set Analysis without selection

swuehl — Mon, 01 Feb 2016 15:02:56 GMT

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.

Re: Set Analysis without selection

Mon, 01 Feb 2016 15:31:36 GMT

Yes I'm ok, I made that :

Change the dimension to Article.

Change the expression to If (sum(CA) > 0 , 1 , 0)

And Change the Total to "Sum of Rows"

The Total il 1963. It's the good Total.

But with the other Expression, and the group, the Total is 8697....

Re: Set Analysis without selection

swuehl — Mon, 01 Feb 2016 16:02:54 GMT

Why do you want the GRP dimension field in the advanced aggregation when you are not using it in your chart?

With only Article as dimension, you can remove the GRP dimension field:

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

Maybe I am just missing what you are trying to do.

Re: Set Analysis without selection

Mon, 01 Feb 2016 16:08:03 GMT

No I want to see result by GRP but calculation need to be effective at the article level.

So to verify, we can test with other expression and Article in dimension.

And normaly, we should have the same result between the two expression.

1963.

Do you understand what I mean ?