topic Re: repeating aggr value in straight table in QlikView

repeating aggr value in straight table

harleen_singh — Fri, 23 Sep 2016 10:44:27 GMT

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

Anonymous — Fri, 23 Sep 2016 10:53:32 GMT

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

Re: repeating aggr value in straight table

settu_periasamy — Fri, 23 Sep 2016 11:01:29 GMT

try this

count(distinct Total EmpID)

Re: repeating aggr value in straight table

rubenmarin — Fri, 23 Sep 2016 11:15:24 GMT

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

nagarjuna_kotha — Fri, 23 Sep 2016 11:31:56 GMT

For above data what is ur expected output ?

Re: repeating aggr value in straight table

sunny_talwar — Fri, 23 Sep 2016 11:46:33 GMT

May be like this:

Count(TOTAL <Year> Headcount)

Re: repeating aggr value in straight table

sunny_talwar — Fri, 23 Sep 2016 11:47:21 GMT

Or this

Count(Distinct TOTAL <Year> EmpID)

Re: repeating aggr value in straight table

sunny_talwar — Fri, 23 Sep 2016 11:48:15 GMT

Or you can do like this with Aggr()

Aggr(NODISTINCT Count(Distinct EmpID), Year)

Re: repeating aggr value in straight table

harleen_singh — Fri, 23 Sep 2016 11:52:38 GMT

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

antoniotiman — Fri, 23 Sep 2016 11:53:30 GMT

Try

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

Re: repeating aggr value in straight table

rubenmarin — Fri, 23 Sep 2016 11:58:02 GMT

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

sunny_talwar — Fri, 23 Sep 2016 12:04:23 GMT

Ruben, is this not going to work?

Count(Distinct TOTAL <Year> EmpID)

Re: repeating aggr value in straight table

Anil_Babu_Samineni — Fri, 23 Sep 2016 12:13:24 GMT

Please refer this,

https://community.qlik.com/docs/DOC-8242

As i Understand, He don't have Total Column on his qvw, Might be you are expecting Total using three Dim's. I hope this helpful.

Re: repeating aggr value in straight table

rubenmarin — Fri, 23 Sep 2016 12:16:25 GMT

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

harleen_singh — Fri, 23 Sep 2016 12:19:32 GMT

thanks Ruben