repeating aggr value in straight table

[harleen_singh]

I Have a straight table like this. I want to repeat values in a column which is total of Asia+America using expression

let say if i use this formula  aggr( count(distinct EmpID),Year). it only returns one value .i.e 35 and 70 but doesn't repeat same in all the columns?

Any ideas?

Year	Region	Headcount	Total
2016	Asia	15	35
2016	America	20	35
2015	Asia	30	70
2015	America	40	70

thanks

[Anonymous]

what is your expected Output? you want 105 for both years and both continents?

[settu_periasamy]

try this

count(distinct Total EmpID)

[rubenmarin]

Hi Harleen, the Aggr parameter is 'Year' so it will return one value for each year, and each row will get the value of his year:

- Rows with Year=2016 get '35'

- Rows with year=2015 get '70'

If you want '105' on all rows the expression given by Settu should work.

[nagarjuna_kotha]

For above data what is ur expected output ?

[sunny_talwar]

May be like this:

Count(TOTAL <Year> Headcount)

[sunny_talwar]

Or this

Count(Distinct TOTAL <Year> EmpID)

[sunny_talwar]

Or you can do like this with Aggr()

Aggr(NODISTINCT Count(Distinct EmpID), Year)

[harleen_singh]

Output i want is following. Total row you see here is the output i needed.

Year	Region	Headcount	Total
2016	Asia	15	35
2016	America	20	35
2015	Asia	30	70
2015	America	40	70

[antoniotiman]

Try

RangeSum(Top(Sum(Headcount),1,NoOfRows()))