topic function inside set analysis in QlikView

function inside set analysis

Mon, 23 May 2011 20:15:00 GMT

I would like to calculate the number of occurences that only one visit was done by a certain representative.

Normally an easy set analysis would do the trick, but the amount of visits are calculated through an expression already "count({<visitsyear={$(selectedyear)}>}visitatlocation)", which makes it impossible to use set analysis. Turning the expression into a variable and then making a new set analysis doesn't work either.

To me it feels like you would do a countif in excel, yet i cannot find an alternative for this.

Representative Company Visits

John A 4

John B 2

John C 1

John D 1

So the question is what other options do I have? Aggr?

Thanks in advance!

function inside set analysis

yasus — Mon, 23 May 2011 20:27:42 GMT

I'm not clear what you are trying to achieve. Could you please describe how the result column should look like in your example?

function inside set analysis

Mon, 23 May 2011 20:37:56 GMT

It should be a single value expressing the amount of occurences that only 1 visits was given to a certain company by a specific sales representative. So in the example: the answer should be 2.

At first I tried this:

count({<visitsyear={$(selectedyear),count(visitlocation)={'1'}>}visitatlocation), yet QV doesnt allow a count in the modifier.

function inside set analysis

erichshiino — Mon, 23 May 2011 20:40:47 GMT

Hi,

Try this:

sum ( aggr (

if(

count({<visitsyear={$(selectedyear)}>}visitatlocation) = 1, 1,0)

, Representative, Company)

)

function inside set analysis

Mon, 23 May 2011 20:51:56 GMT

Yep it has worked so far. Thanks! Now I am going to expand the complexity!