topic Re: Variables and nested set analysis ? in QlikView

Variables and nested set analysis ?

Sahal — Tue, 16 Feb 2016 15:08:16 GMT

Hi,

I have created a variable that holds a set analys in it:

vL.tmp

(IF([Test] = 'Hello', SUM({$<[Flag1] = {1} >} Time) - SUM({$<[Flag2] = {1} >} Time), SUM({$<[Flag3] = {1} >} Time)))

Now i'm trying to nested this variable with another set analyse but i do not get my wanted result.

COUNT({$< '$(vL.tmp)' -={0} >} Users )

I'm trying to exclude users if the vL.tmp = 0

Can it be that my first variable don´t have a aggregation around it?, i do get a result from vL.tmp if i use a textbox

Re: Variables and nested set analysis ?

sunny_talwar — Tue, 16 Feb 2016 16:45:11 GMT

AFAIK the left side of the set modifier cannot have functions. Can you share a sample of what you doing and may be we can help you better?

Re: Variables and nested set analysis ?

stigchel — Tue, 16 Feb 2016 16:51:30 GMT

As Sunny said the left of the set modifier cannot have functions, only fields. What you can use is an expression search in set analysis, for example

=Count( {<Users= {"=Sum(Value) > 0"} >} Users)

Note the double quotes, any expression that is evaluated to true can be used on the right side. In your case you will have to make the right side variable to account for the different situations, or use different expressions within some if statements.

Re: Variables and nested set analysis ?

settu_periasamy — Tue, 16 Feb 2016 17:34:49 GMT

may be you can try like if($(vL.tmp)<>0, Count(Users))

Re: Variables and nested set analysis ?

Oleg_Troyansky — Tue, 16 Feb 2016 17:35:50 GMT

Continuing the line that Sunny started, - you can use your conditional formula stored in a variable, as part of the Advanced Search filter that can be applied to a field. Assuming that your condition needs to be applied to the field User, the filter could look like this:

COUNT({$< User = {"=$(vL.tmp) <>0"} >} Users )

Notice the double quotes (search), the equal sign (advanced search) and the variable doesn't need to be enclosed in single quotes in this case.

cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy!

Re: Variables and nested set analysis ?

Peter_Cammaert — Tue, 16 Feb 2016 17:36:34 GMT

Piet Hein and Sunny already answered your question, so I'll try to explain what was wrong with your initial assumption: in your last set expression, the lefthand side of the modifier (if it would work, but it doesn't) is a single result of an aggregation function and not something that can be regarded as a set or a set container (like a field can be). You are using the compact set operator -= to subtract a set containing a single value {0} from ... what?

Actually a set modifier has a very simple design:

and your use of the compact operator in

is actually identical to

All three elements in this set expression should somehow represent a set of zero or more values. And a set is just a collection of values, even when coded as an advanced search expression. However, the equal sign is an assignment operator, not a comparison operator. And you can only assign values or expression results to an object that can be modified. A field is such an object. A fixed numercial value that results from an aggregation expression is not.

Hope this helps

Peter

Re: Variables and nested set analysis ?

Sahal — Wed, 17 Feb 2016 13:19:51 GMT

Im still having problems here and i will try to explain more.

In my first picture i have the Nr and the User as dimensions

2 expressions vL.tmp and the value in the variable it self as the second expression

I do not understand why im getting 2 different values from my variable and the "value in the variable"

This can be one of the problems i'm facing and that is why i'm not getting my desired result?

The second picture i used the variable vL.tmp for my set analysis but this did not work.

with COUNT({$< User = {"=$(vL.tmp) <>0"} >} Users )

like you see in my list i do not get anything ?

Is the syntax wrong or can it be something else? .

I would like to exlude 1057 - Rafael Martinez Velez from my count of users beause he have 0 time.

having this said i do understand why im can not in this case exlude 1057 because his time is 706,86 but i do not understand what my vL.tmp is doing wrong?, and what the set analys is doing wrong?

Re: Variables and nested set analysis ?

Oleg_Troyansky — Wed, 17 Feb 2016 16:46:29 GMT

It would be a lot easier to troubleshoot if you could post your application or a small sample...

Based on the pictures, the formula in your variable vL.tmp begins with the equal sign, and it's being evaluated only once, outside of the chart - it means, that it's NOT being evaluated in every row of the chart.

In order to make this variable "Dimensional", i.e. allow it to be evaluated in every row of the chart, you should remove the equal sign from the variable AND enclose the variable into the $-sign expression in chart:

$(vL.tmp)

Also, as a side comment, - if you leave the Expression Label empty, you will see the formula fully expanded,- your variables will get expanded, and you will see the final expression.

Anyone who is confused about the various uses of variables, should listen to Rob Wunderlich's lecture on Advanced Scripting at the Masters Summit for Qlik. Rob doesn't leave any room for confusion anymore...

cheers,

Oleg Troyansky