topic Set analysis comparing fields in QlikView

Set analysis comparing fields

Thu, 11 Sep 2014 16:59:00 GMT

I have two date fields say datex and datey.

I want to get count of a certain field when datex=datey

I have been trying count(distinct {<datex=datey>} myfield) but it doesnt work.

Re: Set analysis comparing fields

cspencer3 — Thu, 11 Sep 2014 18:31:31 GMT

is this something you could flag in your load script. Such as saying:

DateTableExample:

LOAD

datex,

datey,

if(datex = datey, 1, 0) as Flag

;

Then you can do Count({<Flag = {1}>}DISTINCT MyField).

Also, i am unsure if it matters but i always put the DISTINCT modifier right before my field name, not before the set analysis.

Re: Set analysis comparing fields

jaimeaguilar — Thu, 11 Sep 2014 18:38:00 GMT

Hi,

if you want to do this with set analysis the correct syntax would be:

count(distinct {<datex={"=datey"}>} myfield)

you were missing the { } for the subset you're looking for as well as the double quotes for telling QlikView that you're performing a search and the equal sign,

regards

Re: Set analysis comparing fields

maxgro — Thu, 11 Sep 2014 20:03:10 GMT

maybe

with set analysis

count(distinct {<dimension= {"=(datex=datey)" } >} myfield)

dimension should not be datex datey

without

count(distinct if(datex=datey, myfield))

Re: Set analysis comparing fields

Thu, 11 Sep 2014 20:22:20 GMT

Difficult to say exactly without some more information on what you're trying to achieve, but you should take a look at the P() function in the manual and see if that helps:

count(distinct {<datex=P(datey)>} myfield)

From the manual:

Set Modifiers with implicit field value definitions

In the above examples, all field values have been explicitly defined or defined through searches. There is however an additional way to define a set of field values by the use of a nested set definition.

In such cases, the element functions P() and E() must be used, representing the element set of possible values and the excluded values of a field, respectively. Inside the brackets, it is possible to specify one set expression and one field, e.g. P({1} Customer). These functions cannot be used in other expressions:

Examples:

sum( {$<Customer = P({1<Product={‘Shoe’}>} Customer)>} Sales )
returns the sales for current selection, but only those customers that ever have bought the product ‘Shoe’. The element function P( ) here returns a list of possible customers; those that are implied by the selection ‘Shoe’ in the field Product.

sum( {$<Customer = P({1<Product={‘Shoe’}>})>} Sales )
same as above. If the field in the element function is omitted, the function will return the possible values of the field specified in the outer assignment.

sum( {$<Customer = P({1<Product={‘Shoe’}>} Supplier)>} Sales )
returns the sales for current selection, but only those customers that ever have supplied the product ‘Shoe’. The element function P( ) here returns a list of possible suppliers; those that are implied by the selection ‘Shoe’ in the field Product. The list of suppliers is then used as a selection in the field Customer.

sum( {$<Customer = E({1<Product={‘Shoe’}>})>} Sales )
returns the sales for current selection, but only those customers that never bought the product ‘Shoe’. The element function E( ) here returns the list of excluded customers; those that are excluded by the selection ‘Shoe’ in the field Product.

Re: Set analysis comparing fields

mikecrengland — Thu, 11 Sep 2014 21:40:54 GMT

Set Analysis is evaluated once per chart, not by row. Charles has the right solution.

mike

www.fortunecookiebi.com

Re: Set analysis comparing fields

jaimeaguilar — Thu, 11 Sep 2014 21:56:26 GMT

In general what you say it's true, Set Analysis is evaluated once per chart, however with advanced searches inside set analysis you can filter just the rows you're looking for, Just like Massimo said, so it can also be done with set analysis,

regards