topic Re: Taking selections into consideration in Set Analysis in QlikView

Taking selections into consideration in Set Analysis

Thu, 03 Oct 2013 11:47:25 GMT

I have this problem that I have added a field into a Set Analysis to calculate a percentage value. The Expression can be viewed below.

=num(sum({<License = {'License'},[Forecast Category]={'Won'},OppYear={$(=vYear)},[Named Account]={'JA'}>} [Opportunity Amount])/sum({<License = {'License'},[Forecast Category]={'Won'},OppYear={$(=vYear)}>} [Opportunity Amount]),'##0%')

So I am making a summation of the Opportunity Amount for all Opportunities where Named Account = JA and divide it with the Total Opportunity Amount.

I want this to be dynamic for selections. As it is now, if I make a selection in a list box for the Named Account field the summation with the [Named Account]={'JA'} is not changing value.

I wonder if anyone knows how to write the expression so that it is dynamic for selections in the Named Account field.

Thank you for any help!

Re: Taking selections into consideration in Set Analysis

israrkhan — Thu, 03 Oct 2013 11:52:16 GMT

try this.

=num(sum({<License = {'License'},[Forecast Category]={'Won'},OppYear={$(=vYear)}>} [Opportunity Amount]) / sum(1{<License = {'License'},[Forecast Category]={'Won'},OppYear={$(=vYear)}>} [Opportunity Amount]),'##0%')

Re: Taking selections into consideration in Set Analysis

Anonymous — Thu, 03 Oct 2013 12:05:02 GMT

Create a variable like below

=GetFieldSelections([Named Account])

and call that variable in the expression like.

=num(sum({<License = {'License'},[Forecast Category]={'Won'},OppYear={$(=vYear)},[Named Account]={'$(variable)'}>} [Opportunity Amount])/sum({<License = {'License'},[Forecast Category]={'Won'},OppYear={$(=vYear)}>} [Opportunity Amount]),'##0%')

hope that helps

Re: Taking selections into consideration in Set Analysis

Thu, 03 Oct 2013 12:30:49 GMT

Create a variable and use it.

Var=GetFieldSelections([Named Account])

use that variable in the expression. So if you select one any of the list box this will change accordingly. But apply one value always select in [Name Account] list box. Because if u don't do that then if no value select then null would be return. And expression become wrong.

=num(sum({<License = {'License'},[Forecast Category]={'Won'},OppYear={$(=vYear)},[Named Account]={'$(Var)'}>} [Opportunity Amount])/sum({<License = {'License'},[Forecast Category]={'Won'},OppYear={$(=vYear)}>} [Opportunity Amount]),'##0%')

Hope this would help you.

Re: Taking selections into consideration in Set Analysis

Thu, 03 Oct 2013 12:44:28 GMT

Fredrik,

To me, to get all named accounts in the divider :" [Named Account] = ,"

But if you set Named Account with JA or with All in the divider, it will not depend anymore on the selection you make in the selector. If you remove it (JA), it will depend again on the ListBox.

Another thing, it could happe that you get a ListBox not on Named Account but on sth similar and depend like Key Account or another linked dimension (Group of Account). In that case it can be misleading : you should remove them in the set with " [group of account]= , "

Fabrice