Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a QV file that has a straight table that I'm using to keep track of the target attendees we've met at conferences. One column lists the "Target Attendees", one column lists the salesperson that met the attendee and the last column sums up the "Total people met" at the conference (in my excel, next to each attendee I have a 1 if we met them or a 0 if we ended up not meeting them). If two different sales people met the same attendee, I don't want it to count that attendee twice, thus inflating my total for "Total People Met". I would like to calculate the total that is displayed by not adding up all the 1's, but only by adding a single "1" per attendee and excluding all the 0's (so I can't use Count (Attendees)). Can you calculate a total rather than using an expression total or a count of rows? And do you know of an expression I can use that will sum or count the attendees the way I described?
For example I want:
Salesperson | Target Attendee | Total People Met |
---|---|---|
(Total) 2 | ||
Jason | Bob L. | 1 |
Jack | Bob L. | 1 |
Jill | Jaime H. | 0 |
Amy | Carol P. | 1 |
Max | George S. | 0 |
Use the concept of dimensionality:
=If(Dimensionality() = 0,Sum(Aggr([Total People Met], [Target Attendee])), Sum([Total People Met]))
Maybe something like
=count({<[Total People Met] = {1}>} DISTINCT [Target Attendee])
Use the concept of dimensionality:
=If(Dimensionality() = 0,Sum(Aggr([Total People Met], [Target Attendee])), Sum([Total People Met]))
That worked perfectly! Thank you!
Awesome