Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Hi,
Try this:
sum ( aggr (
if(
count({<visitsyear={$(selectedyear)}>}visitatlocation) = 1, 1,0)
, Representative, Company)
)
I'm not clear what you are trying to achieve. Could you please describe how the result column should look like in your example?
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.
Hi,
Try this:
sum ( aggr (
if(
count({<visitsyear={$(selectedyear)}>}visitatlocation) = 1, 1,0)
, Representative, Company)
)
Yep it has worked so far. Thanks! Now I am going to expand the complexity!