Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
uridiumabs
Not applicable

Problems with set analysis

Hello,

I need to show a column in a Pivot Table wich counts some rows depending on a condition:

the formula is:  Count(Distinct  {$<[$(variable1)+$(variable2)]= {'>0'}>} [CommercialSite])

So I want to count comercial sites that variable1 greater than 0 and variable2 greater than 0. This sentence is not working because is counting all rows as if no condition is set.

I have checked the syntax and for me is ok, Can anybody help me with this?

Thank you very much.

22 Replies
rittermd
Not applicable

Re: Problems with set analysis

I am thinking that you might want to break up the set analysis into 2 parts.

Variable1>0,Variable2>0

Not the correct syntax.  But just separate them as if 2 different expressions.

mkelemen
Not applicable

Re: Problems with set analysis

Hi Mark,

what you are suggesting is not the same. For example your suggestion does not work for v1=2, v2=-1.

Edit: the text says what v1>0 and v2>0 though - so it is ok

  Matus

sunny_talwar
Not applicable

Re: Problems with set analysis

May be this:

Count(DISTINCT {$<CommercialSite = {"=$(variable1) > 0 and $(variable2)>0"}>} [CommercialSite])

eduardo_dimperi
Not applicable

Re: Problems with set analysis

Hi Juan,

I was reading about, and i believe that when you use (variable1)+$(variable2), that creates a single group so if your have value >0 in variable1 OR variable2, that increase your count.

I'll figure out how solve, but the problem is basically that, as long i had read

uridiumabs
Not applicable

Re: Problems with set analysis

Hi,

I've read all your answers, and changed the sentence in this way:

Count(Distinct {$<[$(variable1)]= {'>0'}, [$(variable2)]= {'>0'}>}  [CommercialSite])

Let me clarify, I just want to count the commercial sites that variable1 is greater than zero and variable2 is greater than zero, imagin that variable1 and variable2 are sales amount and whant to count comercial sites thas have sales.

The output is the same, I get the sum off all commercial sites no matter if they have sales or not.

What I see is that if I use the fields of the table instead variables ths sentence is working OK.

So i guess the problem is not in the sentence but in the use of variables but I've read that using variables is alowed in set analysis.

Any idea?

mkelemen
Not applicable

Re: Problems with set analysis

Hi,

I tested the formula in QS 3.1.4 and it gives me correct result.

How do you define the variables?

  Matus

uridiumabs
Not applicable

Re: Problems with set analysis

Hi Matus,

Variables are just defined as SUM(field).

My mistake I said variable1 and variable 2 and actually meant OR.  So I want to count the commercial sites that variable1 is greater than zero OR variable2 is greater than zero.

Is that posssible in set analysis.

Thanks a lot.

mkelemen
Not applicable

Re: Problems with set analysis

The important part is that the formula holds Sum() function and not a field name.

The formula you posted above works only if you have field names in the variables.

Take the formula from Sunny T and Change 'and' to 'or'.

Count(DISTINCT {$<CommercialSite = {"=$(variable1) > 0 or $(variable2)>0"}>} [CommercialSite])

that works with Sum() inside.

sunny_talwar
Not applicable

Re: Problems with set analysis

Or this:

Count(DISTINCT {$<CommercialSite = {"=RangeSum($(variable1), $(variable2)) > 0"}>} [CommercialSite])