topic using a formula variable in set analysis in QlikView

using a formula variable in set analysis

mjt_childrens — Mon, 03 Sep 2012 19:41:28 GMT

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.

Re: using a formula variable in set analysis

Mon, 03 Sep 2012 20:12:35 GMT

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

Re: using a formula variable in set analysis

pgalvezt — Mon, 03 Sep 2012 20:13:50 GMT

Try this:

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

Hope Help you!

Re: using a formula variable in set analysis

mjt_childrens — Mon, 03 Sep 2012 20:29:41 GMT

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.

Re: using a formula variable in set analysis

cesaraccardi — Mon, 03 Sep 2012 20:50:59 GMT

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

Re: using a formula variable in set analysis

mjt_childrens — Tue, 04 Sep 2012 01:18:33 GMT

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.

Re: using a formula variable in set analysis

whiteline — Tue, 04 Sep 2012 05:21:35 GMT

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

Re: using a formula variable in set analysis

mjt_childrens — Tue, 04 Sep 2012 16:25:31 GMT

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.

Re: using a formula variable in set analysis

Tue, 04 Sep 2012 18:58:16 GMT

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)'}