Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

mjt_childrens
New Contributor III

using a formula variable in set analysis

Hello,

I am having problems correctly using a formula variable in set analysis.    In my application I have defined:

     Variable:  vPeerGroup

     Value:  only({$<EmpPeerSeq={$(=min(EmpPeerSeq))

The formula for the variable appears to evaluate correctly, I can view the value of the variable in a text box with the formula =$(vPeerGroup)

The value will always be text, not numeric.

I'm trying to use this variable in a set analysis equation and cannot seem to get the syntax correct.

 

=sum({$<QtrSum_MetricID={B20}, QtrSum_PeerGroup={'$(vPeerGroup)'}>} QtrSum_PA_Count)

I can hard-code a Peer Group name, ie, QtrSum_PeerGroup={'Branch'} and it works fine.   This is my first time using a variable that is defined as a formula -- there must be something I'm missing regarding correct syntax.  

Thanks.

Tags (3)
1 Solution

Accepted Solutions
mjt_childrens
New Contributor III

Re: using a formula variable in set analysis

I found the answer to my problem.    I needed to put an = in front of my variable value in the variable definition.   Once I made this change to the definition of the variable my formula works correctly with the following format:

  =sum({$<QtrSum_MetricID={B20}, QtrSum_PeerGroup={$(=vPeerGroup)}>} QtrSum_PA_Count)

Thanks for everyone's advice.  

8 Replies
Not applicable

Re: using a formula variable in set analysis

Hi,

two things I recognized at a glance:

I miss some brackets-stuff at the end of your vPeerGroup; only a copy/paste - error through posting?

only({$<EmpPeerSeq={$(=min(EmpPeerSeq)) }>}) (or sth like that)

second:

Think, it should be with an equal sign, as you use a function in your variable

QtrSum_PeerGroup={'$(=vPeerGroup)'}

HtH

Roland

pgalvezt
Valued Contributor

Re: using a formula variable in set analysis

Try this:

=sum({$<QtrSum_MetricID={'B20'}, QtrSum_PeerGroup={'$(vPeerGroup)'}>} QtrSum_PA_Count)

Hope Help you!

mjt_childrens
New Contributor III

Re: using a formula variable in set analysis

Roland, you are correct, cut/paste from Qlikview is lopping off part of my variable formula.  Complete variable formula is:

 

only ({$<EmpPeerSeq={$(=min(EmpPeerSeq))}>} EmpPeerGroup)

I tried your suggestion and the suggestion from pgalvezt but it still doesn't evaluate the variable correctly.    If I replace the variable with the formula it works correctly:

 

=sum({$<QtrSum_MetricID={B20}, QtrSum_PeerGroup={$(=only({$<EmpPeerSeq={$(=min(EmpPeerSeq))}>} EmpPeerGroup))}>} QtrSum_PA_Count)

I'm going to use this in multiple places so I'd like to be able to store the formula as a variable.   I just can't seem to get the syntax correct.

Thanks.

cesaraccardi
Valued Contributor

Re: using a formula variable in set analysis

Hi,

You said the variable doesn't evaluate correctly when you put in the modifier expression, have you tried to create an expression with just the variable? Does it returns any values, I mean can you see the variable evaluation for each line you have in your chart?

Regards,

Cesar Accardi

mjt_childrens
New Contributor III

Re: using a formula variable in set analysis

Hi Cesar,

Yes, I have tried to get my set analysis expression to work only using the variable but get no results.   The variable returns a value when I place it in a text box by itself.   The only place I can't seem to get it to work correctly is when I use it within a set analysis statement.   This leads me to believe it is related to the syntax.   I've tried all sorts of combinations and haven't found the "magic format".

Thanks.

whiteline
Honored Contributor II

Re: using a formula variable in set analysis

=sum({$<QtrSum_MetricID={"B20"}, QtrSum_PeerGroup={"=$(vPeerGroup)"}>} QtrSum_PA_Count)

mjt_childrens
New Contributor III

Re: using a formula variable in set analysis

I found the answer to my problem.    I needed to put an = in front of my variable value in the variable definition.   Once I made this change to the definition of the variable my formula works correctly with the following format:

  =sum({$<QtrSum_MetricID={B20}, QtrSum_PeerGroup={$(=vPeerGroup)}>} QtrSum_PA_Count)

Thanks for everyone's advice.  

Not applicable

Re: using a formula variable in set analysis

Hi Thompson,

never give up. Sometimes live is easy. I wasn't far away in my first post:

second:

Think, it should be with an equal sign, as you use a function in your variable

QtrSum_PeerGroup={'$(=vPeerGroup)'}

RR

Community Browser