Taking selections into consideration in Set Analysis

[#1]

Hi

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!

[israrkhan]

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%')

[Anonymous]

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

[#4]

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.

[#5]

Fredrik,

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

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

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