getfieldselections with multiple value (using in set analysis)

[#1]

Hi All,

I need some help on how to use the getfieldselections with multiple value in set analysis use. I have created a list box of different stages: stage1, stage2, stage3...stage5

I need to create another chart to show how many opportunities we get from each of the stage. I want to make this more dynamically and flexible. So if none of the stages from the list box are selected, I want to show the quantatity of the opportunity we have for stage6. Otherwise,  show me the quantatity of whichever stages are selected. It works fine if we select a single stage..But I want to know how to make it work so it can show multiple stages opportunity count. Thanks a lot for all the help in advance.

vSelStage: =if(isnull(GetFieldSelections(STAGENAME)),'Stage6',GetFieldSelections(STAGENAME))

count(stagename={<"$(vSelStage)">}opportunity_id

[anbu1984]

=if(isnull(GetFieldSelections(STAGENAME)),'Stage6',Concat(Chr(39) & STAGENAME & Chr(39),','))

count({<stagename={"$(vSelStage)"}>}opportunity_id)

Or

=if(isnull(GetFieldSelections(STAGENAME)),count({<stagename={'Stage6'}>}opportunity_id),count(opportunity_id))

[#3]

I miss a value at the isnull statement. Isnull always returns a value, either 0 or -1. So I don't understand why it is such in your expression.

Could you try what happens if you just write STAGENAME as the second option of your If statement?

vSelStage: =if(count(distinct GetFieldSelections(STAGENAME)=1,'Stage6',STAGENAME))

count({<stagename={"$(vSelStage)"}>}opportunity_id)

Let me know whether it worked or not!

Best,

T

[marcus_sommer]

You could create these expression directly without a variable and the check if any value is selected is better with getselectedcount(). Try something like this:

count({< STAGENAME {$(=if(GetSelectedCount(STAGENAME) = 0,  '*',

     GetFieldSelections(STAGENAME, ', ', 12)))}>} opportunity_id)

And be carefully with fieldnames: STAGENAME and stagename isn't the same and wouldn't be match.

- Marcus

[#5 · Accepted solution]

Hi Anbu,

Thanks for your response. I got rid of using getfieldselection and used the concat function instead. And it worked. See below. Thanks again.

J

Count({<STAGENAME={$(=concat(DISTINCT chr(39) & STAGENAME & chr(39),','))}
>} OPPORTUNITY_ID

[#6]

Hi Tibor,

I tried your expression: =if(count(distinct GetFieldSelections(STAGENAME)=1,'Stage6',STAGENAME))

it would always give me 'Stage6' no mater which stage I click in the listbox. I think that's because when you click the STAGENAME listbox, count(distinct getfieldselections(stagename) would always give your the answer '1'..So this did not work for my case. ifnull() seem works in the if function.

I tried the expression below and it worked for me.

Count({<STAGENAME={$(=concat(DISTINCT chr(39) & STAGENAME & chr(39),','))}
>} OPPORTUNITY_ID

Thanks for your help.

[#7]

Hi Marcus,

Smart way to solve this. But I don't think getfieldselection works well for multiple values in set analysis. Seems like concat is a good way to deal with this and it solved my problem. See below. Thank for your help.

Count({<STAGENAME={$(=concat(DISTINCT chr(39) & STAGENAME & chr(39),','))}
>} OPPORTUNITY_ID

[anbu1984]

Please close the post, if your queries are answered

[marcus_sommer]

For me it worked in this way with multiple values well and you need not neccessary handle the values-format if it's a string or numeric. I'm not sure but I think in larger data-sets getfieldselections() would be better perform as a aggregation-function.

- Marcus