Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

felcar2013
Contributor III

IF and set analysis

hi

somebody knows how this IF-statement would look like in set analysis?

if(x= 'ab' and y='cd', 0, count distinct(z))

thanks for the help

12 Replies
martin59
Valued Contributor II

Re: IF and set analysis

Hi,

Your expression is wrong.

It can be

if(x='ab' and y='cd', 0, Count(Distinct z))

or

Count(Distinct if(x='ab' and y='cd', z))

Only the 2nd may be translated in Set Analysis :

Count({<x={ab}, y={cd}>} Distinct z)

Hope that helps you

Martin Favier

felcar2013
Contributor III

Re: IF and set analysis

hi Martin

thanks. I tried already that way. it works, but i want to set a condition to the intersection of x and y (two dimensions), where the values are "ab" AND "cd".

other values of the dimension are not showed if i use the set analysis, but i want to see everything. Like this:

the dimensions are X, Y and are assigned different values. I want that when X=ab AND Y = cd, then it should show zero, otherwise, it should count the rest and show them in the fields

Header 1Header 2Header 3Header 4
Y = aY=c
Y=cd
X = e451212
X = u789874
X= ab121445

Final result shall look like

Header 1Header 2Header 3Header 4
Y = aY=c
Y=cd
X = e451212
X = u789874
X= ab12140
martin59
Valued Contributor II

Re: IF and set analysis

Ok I understand,

so it's a little bit more complicated.

Have you tried this (if we consider ID as the Primary Key) ?

Count({<ID=E({<x={ab}, y={cd}>} ID)>} Distinct z)

This expression depends on your datamodel.

Hope that helps you

Martin Favier

martin59
Valued Contributor II

Re: IF and set analysis

Ok I understand,

so it's a little bit more complicated.

Have you tried this (if we consider ID as the Primary Key) ?

Count({<ID=E({<x={ab}, y={cd}>} ID)>} Distinct z)

This expression depends on your datamodel.

Hope that helps you

Martin Favier

martin59
Valued Contributor II

Re: IF and set analysis

Ok I understand,

so it's a little bit more complicated.

Have you tried this (if we consider ID as the Primary Key) ?

Count({<ID=E({<x={ab}, y={cd}>} ID)>} Distinct z)

This expression depends on your datamodel.

Hope that helps you

Martin Favier

felcar2013
Contributor III

Re: IF and set analysis

thanks

i tried, but the result is nothing (the formula shows no errors, but i do not get any number, i thinking i am making a logical error)

my primary key in the data structure is contact_id, which is at the same time the field i am counting

i imagine with this formula, i am counting the total number of customers, excluding the customers which fall into the intersection of segments:

Count({$<contact_id = e({<[Segment_1] ={'NewCustomer'},[Segment_2]={'LostCustomer'}>} contact_id) DISTINCT contact_id)

Segment_1 = X

Segment_2 = Y

and

'NewCustomer' = 'ab'

'LostCustomer' = 'cd'

the contact_id would be ID and Z

what is wrong?

martin59
Valued Contributor II

Re: IF and set analysis

Are all your contact_id in NewCustomer AND LostCustomer ?

Martin Favier

martin59
Valued Contributor II

Re: IF and set analysis

Are all your contact_id in NewCustomer AND LostCustomer ?

Martin Favier

martin59
Valued Contributor II

Re: IF and set analysis

Are all your contact_id in NewCustomer AND LostCustomer ?

Martin Favier