topic Re: Set analysis with dynamic variable in App Development

Set analysis with dynamic variable

wayne-a — Wed, 13 Jun 2018 18:56:59 GMT

Hi, I've been trying to get a set analysis expression working with a variable without any luck. My variable value is set dynamically depending on selection(s) of a dimension. Definition is as follows:

If(GetSelectedCount(Test_ID)<> 1,('SYP' & ',' & 'RPR'),

If(MixMatch(GetFieldSelections(Test_ID),'SYP'),('SYP'),

If(MixMatch(GetFieldSelections(Test_ID),'RPR'),('RPR'),('SYP' & ',' & 'RPR'))))

When I put this value in a KPI it displays as SYP,RPR but if I use it in a set analysis expression like the following it doesn't apply and shows up as a - in the kpi

=Count({$<Positive = {'Y'},Test_ID={$(=vSYPRPR)}>}[Order#])

If I define the variable like the following then the correct values are returned

='SYP,RPR'

Is it not possible to use a dynamically defined variable in set analysis? I've looked up lots of examples of using variables in set analysis and tried the various examples I've seen on the community but have not been able to get this to work.

Thanks in advance for any help or insight.

Re: Set analysis with dynamic variable

sunny_talwar — Wed, 13 Jun 2018 19:29:45 GMT

May be try this

If(GetSelectedCount(Test_ID)<> 1, Chr(39) & 'SYP' & Chr(39) & ',' & Chr(39) & 'RPR' & Chr(39),

If(MixMatch(GetFieldSelections(Test_ID),'SYP'), Chr(39) & 'SYP' & Chr(39),

If(MixMatch(GetFieldSelections(Test_ID),'RPR'), Chr(39) & 'RPR' & Chr(39), Chr(39) & 'SYP' & Chr(39) & ',' & Chr(39) & 'RPR' & Chr(39))))

Re: Set analysis with dynamic variable

wayne-a — Wed, 13 Jun 2018 19:37:25 GMT

Hi Sunny,

Thank you for the reply. When I do that the variable in the KPI looks ok with the quotes around the individual values but in the count expression it doesn't evaluate coming back with a value of -

I tried something similar by concatenating the quotes and received the same results.

('''SYP''' & ',' & '''RPR''')

Wayne

Re: Set analysis with dynamic variable

sunny_talwar — Wed, 13 Jun 2018 19:39:53 GMT

Try removing the = from your set analysis and see if that works

=Count({$<Positive = {'Y'}, Test_ID = {$(vSYPRPR)}>} [Order#])

Re: Set analysis with dynamic variable

wayne-a — Wed, 13 Jun 2018 19:45:48 GMT

Hi Sunny,

Unfortunately same result. Here is the changed statement just to verify I didn't do something wrong.

=Count({$<Positive = {'Y'},Test_ID={$(vSYPCH39)}>}[Order#])

Thanks,

Wayne

Re: Set analysis with dynamic variable

sunny_talwar — Wed, 13 Jun 2018 19:47:32 GMT

So, just so we are on the same page... you used the new expression I gave for your variable and used the above expression without = sign and still didn't get anything? Would you be able to share a sample to check this?

Re: Set analysis with dynamic variable

wayne-a — Wed, 13 Jun 2018 19:53:19 GMT

Hi Sunny,

No worries. Yes, I created a new variable called vSYPCH39 and copied the definition you supplied as follows:

If(GetSelectedCount(Test_ID)<> 1, Chr(39) & 'SYP' & Chr(39) & ',' & Chr(39) & 'RPR' & Chr(39),

If(MixMatch(GetFieldSelections(Test_ID),'SYP'), Chr(39) & 'SYP' & Chr(39),

If(MixMatch(GetFieldSelections(Test_ID),'RPR'), Chr(39) & 'RPR' & Chr(39), Chr(39) & 'SYP' & Chr(39) & ',' & Chr(39) & 'RPR' & Chr(39))))

And the expression is the following:

=Count({$<Positive = {'Y'},Test_ID={$(vSYPCH39)}>}[Order#])

I'll see if I can get a small app together, it has a lot of data.

Re: Set analysis with dynamic variable

wayne-a — Thu, 14 Jun 2018 11:48:32 GMT

Hi Sunny,

I attached an example to my post above. There is a KPI with a value of "-" that is using the variable and expression from the post above.

Thanks again for your help.

Wayne

Re: Set analysis with dynamic variable

sunny_talwar — Thu, 14 Jun 2018 11:50:05 GMT

I am checking

Re: Set analysis with dynamic variable

sunny_talwar — Thu, 14 Jun 2018 12:06:19 GMT

Check the attached

Re: Set analysis with dynamic variable

wayne-a — Thu, 14 Jun 2018 12:33:28 GMT

Ugh, so somewhere in my cutting and pasting I left an "=" sign in the NUM function. I tried it again with some of my other variables but they still didn't work, so I did need the Chr(39) version which was the real issue.

Thanks again for all your help.

Re: Set analysis with dynamic variable

sunny_talwar — Thu, 14 Jun 2018 12:36:53 GMT

Also, added = before the variable vSYPCH39. Removed $() from the KPI variable and removed - sign after Num(...)