Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
what is your expected Output? you want 105 for both years and both continents?
try this
count(distinct Total EmpID)
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.
For above data what is ur expected output ?
May be like this:
Count(TOTAL <Year> Headcount)
Or this
Count(Distinct TOTAL <Year> EmpID)
Or you can do like this with Aggr()
Aggr(NODISTINCT Count(Distinct EmpID), Year)
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 |
Try
RangeSum(Top(Sum(Headcount),1,NoOfRows()))