topic Re: set analysis syntax in QlikView

set analysis syntax

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have difficulties understanding the syntax of set analysis. What does the ' = ' exactly do?

some information:

vShipDate is a variable based on a selection in EDCDate: = only(EDCDate)

all date formats are yyymmdd (eg: 20170911)

why do those formulas give me different results?

=sum({<ExpectedShipDate={'>$(vShipDate)'}, ErsterfassungsDatum={'=$(vShipDate)'}>}BypackQuantity) = 136.892

=sum({<ExpectedShipDate={'>$(vShipDate)'}, ErsterfassungsDatum={'$(vShipDate)'}>}BypackQuantity) = 124.725

I only want to sum quantities which fulfill both conditions.

thanks,

Lucas

Re: set analysis syntax

Anil_Babu_Samineni — Mon, 18 Sep 2017 15:39:30 GMT

Looks like you restrict same variable. Can you try this?

ErsterfassungsDatum={'$(=vShipDate)'}

Re: set analysis syntax

Anonymous — Mon, 18 Sep 2017 15:51:41 GMT

it gives the same result as the second formula

what do you mean with 'restrict the same variable'?

vShipDate is set with a selection

now i want the sum from datasets where

ErsterfassungsDatum is equal to my selection

and

ExpectedShipDate is larger than my selection

maybe im trying it the wrong way?

Re: set analysis syntax

Anil_Babu_Samineni — Mon, 18 Sep 2017 15:57:02 GMT

What are you getting without variable?

Re: set analysis syntax

Colin-Albert — Mon, 18 Sep 2017 15:57:10 GMT

Try this. Note that the search expression is enclosed in double quotes.

ErsterfassungsDatum={">=$(vShipDate)"}

To check your set expression, add the column to a Straight Table chart with a blank column label.

Then if you hover your mouse on the column header the pop-up will show the expression evaluating the variables.

set analysis syntax

Re: set analysis syntax

Chanty4u — Mon, 18 Sep 2017 15:58:12 GMT

try this

remove= from variable before

=sum({<ExpectedShipDate={'>$(vShipDate)'}, ErsterfassungsDatum={$(=vShipDate)}>}BypackQuantity)

Re: set analysis syntax

Anonymous — Mon, 18 Sep 2017 16:05:30 GMT

it eveluates correctly from what i see:

"=20170911" with the same result as my first formula

ErsterfassungsDatum={'=$(vShipDate)'}

Re: set analysis syntax

Anonymous — Mon, 18 Sep 2017 16:07:56 GMT

this gives the same result as my first formula.

when i hard code it ErsterfassungsDatum = {'20170911'} it gives me the result of my first formula. So i would assume, this is the one I would like to see.

but what does the ' = ' exactly do?

Re: set analysis syntax

Chanty4u — Mon, 18 Sep 2017 16:09:46 GMT

= remove at variable creation place not in expression

Re: set analysis syntax

Colin-Albert — Mon, 18 Sep 2017 16:54:05 GMT

Hi Lucas,

Does your data actually have different BypackQuantaties against different ExpectedShipDates for the same ErsterfassungsDatum value?

What result do you get if you hard-code the dates into a set expression in a new column in your chart?

=sum({<ExpectedShipDate={">20170911"}, ErsterfassungsDatum={20170911}>} BypackQuantity)

and also add two other columns

=sum({<ExpectedShipDate={">20170911"}>} BypackQuantity)

=sum({<ErsterfassungsDatum={20170911}>} BypackQuantity)

Do these three columns have the same values?

This may help identify the problem.

Re: set analysis syntax

MarcoWedel — Mon, 18 Sep 2017 20:15:53 GMT

maybe helpful:

The Little Equals Sign

The Magic of Variables

The Magic of Dollar Expansions

regards

Marco

Re: set analysis syntax

Anonymous — Tue, 19 Sep 2017 07:54:17 GMT

hi Colin

Does your data actually have different BypackQuantaties against different ExpectedShipDates for the same ErsterfassungsDatum value?

what do you mean with different BypackQuantities? yes there is more than one value with the same ErsterfassungsDatum for different ExpectedShipDates.

no, the columns have different values, see screenshot

Re: set analysis syntax

Colin-Albert — Tue, 19 Sep 2017 08:28:12 GMT

Hi Lucas,

Your screen-shots with a blabk column label show the expression details.

Try adding columns with the expressions

sum({<ExpectedShipDate={'>$(vShipDate)'}, ErsterfassungsDatum={'=$(vShipDate)'}>}BypackQuantity)

and

sum({<ExpectedShipDate={'>$(vShipDate)'}, ErsterfassungsDatum={'$(vShipDate)'}>}BypackQuantity)

with blank labels, and you will be able to see how the actual expressions differ when the variables are expanded.

Re: set analysis syntax

Anonymous — Tue, 19 Sep 2017 09:25:58 GMT

Colin,

that was exactly my point

I see the difference but i don't understand it.

first one is

=sum({<ExpectedShipDate={'>$(vShipDate)'}, ErsterfassungsDatum={'=$(vShipDate)'}>}BypackQuantity)

and second is

=sum({<ExpectedShipDate={'>$(vShipDate)'}, ErsterfassungsDatum={'$(vShipDate)'}>}BypackQuantity)

Re: set analysis syntax

Colin-Albert — Tue, 19 Sep 2017 10:56:47 GMT

Your top query using '=20170911' includes values that do not match that date. The first row is 20170908.

Do your results change if you replace the single quotes with double quotes?

Quotes in Set Analysis

Re: set analysis syntax

Anonymous — Tue, 19 Sep 2017 11:48:09 GMT

unfortunately Im stuck at SR3 because I cant update my QV myself. So i got to work with what I got.

So the blog tries to tell me, that search strings like mine " ErsterfassungsDatum={'$(vShipDate)'}"

should actually put into double quotes.

I tried this but it does not show any different results

but you are right, since the top query is showing results that do not match the string, the syntax must be wrong?!?!

Re: set analysis syntax

Colin-Albert — Tue, 19 Sep 2017 11:57:40 GMT

Hi Lucas,

The search string should be ErsterfassungsDatum={"$(vShipDate)"} though the change does not take effect until the Nov2017 release.

I have seen issues when dates are formatted as 2017/09/11 where the equals sign forces the date to be evaluated as 2017 divided by 9 divided by 11 = 20.373737, but this is not the case with your date.

Set analysis is not evaluated line by line so there may be an issue with your expression of only(EDCDate) returning null. Only() returns a value if there is a single value in the field otherwise it returns null. I prefer to use maxstring() or minstring() rather than only() in set expressions as the max functions do not return nulls unless the whole column is null.

Re: set analysis syntax

Anonymous — Tue, 19 Sep 2017 12:10:35 GMT

yes I tried

ErsterfassungsDatum={"=$(vShipDate)"}

and

ErsterfassungsDatum={"$(vShipDate)"}

but the results did not change

I think it has to do with my table structure and DateKeys with the MasterCalendar. But this would take more time to evaluate.