Use Set Analysis into variable

[l_denivelle]

Hi

I'm trying to put a variable into a Set Analysis. The problem is that my variable is the result of another Set Analysis.

Here an example :

I have my first Set Analysis :

LET MyFirstSetAnalysis = ''Avg( { < [Type de date]= { ' & chr(39)& 'Engagement' &chr(39)& '}, [Année]={'&chr(39)&'2015' &chr(39) & '} > } [Délai de réponse])';

Now I want a new Set Analysis which will contain the result of my first Set Analysis :

LET MySecondSetAnalysis= 'Count( { < [Délai] = { >= $(' & MyFirstSetAnalysis & ' ) } > } DISTINCT [Code module])';

If I display my variable I get this :

Count( { [Délai]= {>= } > } DISTINCT [Code Module];

Questions :

- Can we us Set Analysis into another Set Analysis ?

- If yes, how ?

Thank you !

[maximiliano_vel]

The answer is yes, pls try this

LET MyFirstSetAnalysis = Avg( { < [Type de date]= {Engagement}, [Année]={2015} > } [Délai de réponse]);

LET MySecondSetAnalysis= Count( { < [Délai] = { ">= $(MyFirstSetAnalysis)" } > } DISTINCT [Code module])';

[sasiparupudi1]

You can not use $ sign in the varibles in the script

try using @ and replace it in the front end before the use

hth

Sasi

[rubenmarin]

Hi, your first variable needs to start with an '='.

Your second variableneeds doublequotes for set analisys (don't use simple quotes or it will mess with your inserted variable).

It can work, I would first try it simplier trying if this works:

Count( { < [Délai] = {">= $(=Avg( { < [Type de date]= {'Engagement'}, [Année]={'2015'}> } [Délai de réponse]) )"} > } DISTINCT [Code module])

[Kushal_Chawda]

may be try

Count( { < [Délai] = { >= '$(=MyFirstSetAnalysis))' } > } DISTINCT [Code module])

[jonathandienst · Accepted solution]

Is that a double quote or a pair of single quotes at the start of the first variable def? Should be one single quote:

LET MyFirstSetAnalysis = 'Avg( {<[Type de date]= {''Engagement''}, [Année]={2015}>} [Délai de réponse])';

(the embedded quotes are pairs of single quotes to escape the quotes. No need for chr(39))

The second is missing an = sign:

LET MySecondSetAnalysis= 'Count( {<[Délai] = {>=$' & '(=' & MyFirstSetAnalysis & ')}>} DISTINCT [Code module])';

(You need to split the $( to defer the expansion and be careful with spaces inside the set expression, some can break the expression)

[rubenmarin]

Reading Sasidhar I remember than the Dollar will mess the result in script, you'll need to use CHR():

LET MyFirstSetAnalysis = ''=Avg( { < [Type de date]= { ' & chr(39)& 'Engagement' &chr(39)& '}, [Année]={'&chr(39)&'2015' &chr(39) & '} > } [Délai de réponse])';

Now I want a new Set Analysis which will contain the result of my first Set Analysis :

LET MySecondSetAnalysis= 'Count( { < [Délai] = {">= ' & Chr(36) & '(' & MyFirstSetAnalysis & '") } > } DISTINCT [Code module])';

[l_denivelle]

hmm it doesn't work for me.

I can't use LET with your solution or maybe you forgot the quote ?

I tried with SET and I have the same empty result

[l_denivelle]

I didn't understand well. Where exactly I have to put @ ?

I have to replace $ by @ ?

[sasiparupudi1]

Yes,use @ in the place of $ and replace it later when you want to use your variable

LET MySecondSetAnalysis= 'Count( { < [Délai] = { >= @(' & MyFirstSetAnalysis & ' ) } > } DISTINCT [Code module])';

replace(MySecondSetAnalysis,'@','$')