topic Re: Calculate column total instead of using expression total in QlikView

Calculate column total instead of using expression total

Tue, 09 Jun 2015 16:29:25 GMT

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

Re: Calculate column total instead of using expression total

swuehl — Tue, 09 Jun 2015 16:33:32 GMT

Maybe something like

=count({<[Total People Met] = {1}>} DISTINCT [Target Attendee])

Re: Calculate column total instead of using expression total

sunny_talwar — Tue, 09 Jun 2015 16:37:43 GMT

Use the concept of dimensionality:

=If(Dimensionality() = 0,Sum(Aggr([Total People Met], [Target Attendee])), Sum([Total People Met]))

Re: Calculate column total instead of using expression total

Tue, 09 Jun 2015 17:56:17 GMT

That worked perfectly! Thank you!

Re: Calculate column total instead of using expression total

sunny_talwar — Tue, 09 Jun 2015 17:59:52 GMT

Awesome