Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
Try this:
=sum({$<QtrSum_MetricID={'B20'}, QtrSum_PeerGroup={'$(vPeerGroup)'}>} QtrSum_PA_Count)
Hope Help you!
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.
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
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.
=sum({$<QtrSum_MetricID={"B20"}, QtrSum_PeerGroup={"=$(vPeerGroup)"}>} QtrSum_PA_Count)
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.
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