topic Re: getfieldselections with multiple value (using in set analysis) in QlikView

getfieldselections with multiple value (using in set analysis)

Wed, 17 Dec 2014 23:02:51 GMT

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

Re: getfieldselections with multiple value (using in set analysis)

anbu1984 — Thu, 18 Dec 2014 07:10:21 GMT

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

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

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

Re: getfieldselections with multiple value (using in set analysis)

Thu, 18 Dec 2014 09:03:18 GMT

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,

Re: getfieldselections with multiple value (using in set analysis)

marcus_sommer — Thu, 18 Dec 2014 12:39:46 GMT

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

Re: getfieldselections with multiple value (using in set analysis)

Thu, 18 Dec 2014 23:10:46 GMT

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.

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

Re: getfieldselections with multiple value (using in set analysis)

Thu, 18 Dec 2014 23:27:18 GMT

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.

Re: getfieldselections with multiple value (using in set analysis)

Thu, 18 Dec 2014 23:31:36 GMT

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

Re: getfieldselections with multiple value (using in set analysis)

anbu1984 — Fri, 19 Dec 2014 02:39:38 GMT

Please close the post, if your queries are answered

Re: getfieldselections with multiple value (using in set analysis)

marcus_sommer — Fri, 19 Dec 2014 07:40:44 GMT

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