using a formula variable in set analysis

[mjt_childrens]

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.

[#2]

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]

Try this:

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

Hope Help you!

[mjt_childrens]

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]

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]

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]

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

[mjt_childrens · Accepted solution]

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.  

[#9]

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