function inside set analysis

[#1]

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!

[yasus]

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

[#3]

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.

[erichshiino · Accepted solution]

Hi,

Try this:

sum (  aggr (

if(

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

, Representative, Company)

)

[#5]

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