topic Re: (yet another) set analysis with a variable question in App Development

(yet another) set analysis with a variable question

phuelish — Thu, 17 May 2018 18:13:51 GMT

Hi all,

I've looked through about a dozen posts on the forum to try to solve this, but can't seem to figure out the correct syntax. I'm trying to use a variable as a criteria in a set expression. The variable returns a string concatenation based on the selections made in two combo-boxes.

chr(39)&

if(match(pMetric,'Frq-P','Sev-P','Frq-R','PP-P')>0,only(Cov_Prefix),'')&

only(Num_1)&chr(39)

When expanded as =$(vNumFilter), this returns the following string: 'PD_Paid_$', which is correct based on the selections I've made. I'm running into problems when I try to use it in a set expression. I've tried a variety of approaches and can't seem to get it to work:

Sum({<Metric={"=$(vNumFilter)"}>}Result)

Sum({<Metric={"$(vNumFilter)"}>}Result)

Sum({<Metric={=$(vNumFilter)}>}Result)

Sum({<Metric={$(vNumFilter)}>}Result)

Sum({<Metric={$(=vNumFilter)}>}Result)

I've also tried the variable with and without the chr(39) for the single quotes. I've also tried substituting the text string directly just to make sure it's not the variable. the equation works correctly in this format:

Sum({<Metric={'PD_Paid_$'}>}Result)

What am I missing or doing wrong? Thanks for any help you can provide.

-Frank

Re: (yet another) set analysis with a variable question

sunny_talwar — Thu, 17 May 2018 18:22:40 GMT

How about this

Sum({<Metric={$(=$(vNumFilter))}>}Result)

or this

Sum({<Metric={$(vNumFilter)}>}Result)

Re: (yet another) set analysis with a variable question

phuelish — Thu, 17 May 2018 18:28:09 GMT

Unfortunately, no

Re: (yet another) set analysis with a variable question

sunny_talwar — Thu, 17 May 2018 18:32:46 GMT

Did you remove the double quotes?

Re: (yet another) set analysis with a variable question

phuelish — Thu, 17 May 2018 18:39:51 GMT

I did

Re: (yet another) set analysis with a variable question

sunny_talwar — Thu, 17 May 2018 18:41:28 GMT

Would it be possible to share a sample to check this out?

Re: (yet another) set analysis with a variable question

phuelish — Thu, 17 May 2018 19:37:52 GMT

I figured out the problem. Essentially it's a double-expansion problem. I need the variable to expand the possible values using p(), then would need to expand the variable.

I managed to make it work by sticking the variable equation directly inside the set analysis and modified it slightly.

Sum({<Metric={$(=if(match(pMetric,'Frq-P','Sev-P','Frq-R','PP-P')>0,only(Cov_Prefix),'')&only(Num_1))}>}Result)