14 Replies Latest reply: Sep 23, 2016 8:19 AM by Harleen Singh

# repeating aggr value in straight table

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

• ###### Re: repeating aggr value in straight table

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

• ###### Re: repeating aggr value in straight table

try this

count(distinct Total EmpID)

• ###### Re: repeating aggr value in straight table

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.

• ###### Re: repeating aggr value in straight table

For above data what is ur expected output ?

• ###### Re: repeating aggr value in straight table

May be like this:

• ###### Re: repeating aggr value in straight table

Or this

Count(Distinct TOTAL <Year> EmpID)

• ###### Re: repeating aggr value in straight table

Or you can do like this with Aggr()

Aggr(NODISTINCT Count(Distinct EmpID), Year)

• ###### Re: repeating aggr value in straight table

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
• ###### Re: repeating aggr value in straight table

Ok, I understood the opposite, then I think the NODISTINCT option given by sunny is what you are searching for.

• ###### Re: repeating aggr value in straight table

Ruben, is this not going to work?

Count(Distinct TOTAL <Year> EmpID)

• ###### Re: repeating aggr value in straight table

Yes, it can work too, I mentioned the NODISTINCT because it's the more similar to the original expression, it only needs this keyword to repeat values.

But the other two can also work...well, the first one -Count(TOTAL <Year> Headcount)- needs a Sum() intead of a Count()... things of quick-answering i guess.

• ###### Re: repeating aggr value in straight table

thanks Ruben

• ###### Re: repeating aggr value in straight table

Try