Missing a dimension association in Aggr()

[#1]

Hi. I'm new to QlikView and an attempting to translate some tedious Excel operations to QlikView... For example I have the following dataset:

Sales Code	Team	Sales Person	Sales Amount
A1	T1	John	$100
A1	T1	Mary	$100
A2	T1	John	$300
A2	T1	Mary	$300
A2	T2	Paul	$300

Each unique Sales Code is sales, and the Sales Amount is the total amount of the entire sales. So say for A1, both John and Mary did the job for the total amount of $100. Assuming the team split is even for each sales: so for Sales Code A1, Team T1 will be $100, and for sales A2, T1=$150 and T2=$150. Now I'd like to look at the total sum of Team Share by Team, I put in this expression in a straight table chart (with Team as the dimension):

Sum([Sales Amount]/(Aggr(Count(Distinct (Team)), [Sales Amount])))

But the resulting table becomes this only:

Team	Total Share
T1	$250

This figure for T1 is correct ($100 + $150), but T2 just won't show up. Am I making a mistake in Aggr()? Thanks!

[lironbaram]

try this

Sum([Sales Amount]/(Aggr(Count(Distinct (Team)), [Sales Amount],Team)))

otherwise aggr function disregards the table dimension

[sunny_talwar · Accepted solution]

Try this:

=Sum(Aggr(Avg(TOTAL <[Sales Code]> [Sales Amount])/Count(DISTINCT TOTAL <[Sales Code]> Team), [Sales Code], Team))

[#4]

Thanks. That seems a mouthful.. May I know why the Avg() is needed in this case (assuming the TOTAL / Count should already split the total)?

Thanks again!