topic Re: line level count and avg at total is it possible? in QlikView

line level count and avg at total is it possible?

rmuhammad — Wed, 25 Nov 2020 16:16:04 GMT

Hi All

is it possible to count by month and Total would be Average?

Re: line level count and avg at total is it possible?

sunny_talwar — Thu, 14 Dec 2017 19:43:03 GMT

Sure... it would be something like this may be

Avg(Aggr(Count(Measure), SnapYear, SnapMonth))

Re: line level count and avg at total is it possible?

rmuhammad — Thu, 14 Dec 2017 20:40:03 GMT

It didnt work

AVG(aggr(Count( {<[Year/Month],Period={'<=$(=max(Period))'}>} EMPLID),YEAR,MONTH))

with this I got was only Oct (As user has selected Oct) no other months showed

2017 10 122878

I dont know what that 122878 number is..not the AVG...I have all values below 80000

Re: line level count and avg at total is it possible?

vishsaggi — Thu, 14 Dec 2017 20:43:37 GMT

Can you share a sample app to look into?

Re: line level count and avg at total is it possible?

sunny_talwar — Thu, 14 Dec 2017 20:43:37 GMT

First of all, let me ask you this... did it not work at all, or did it not work as your expected? The reason it did not work as expected it because you failed to provide complete information.... do you expect me to know your expression and give you a result that would work for you? I am sorry, but you will have a hard time all the time...

Anyways, try adding the set analysis from your inner aggregation to the outer one and see if this resolves your problem.

Avg({<[Year/Month],Period={'<=$(=max(Period))'}>}Aggr(Count( {<[Year/Month],Period={'<=$(=max(Period))'}>} EMPLID),YEAR,MONTH))

Re: line level count and avg at total is it possible?

rmuhammad — Thu, 14 Dec 2017 21:09:32 GMT

Sunny, Thanks for help. Much appreciated.

Still AGGR() and AVG() producing some results difficult to figure out what they are doing. I have to dig more on these functions. I feel in QV these 2 functions are not straight forward.

Re: line level count and avg at total is it possible?

sunny_talwar — Thu, 14 Dec 2017 21:11:45 GMT

So the value for the row level isn't the right number? or just the totals are off?

Re: line level count and avg at total is it possible?

rmuhammad — Mon, 18 Dec 2017 17:02:23 GMT

Attached is an app I was able to reproduce problem in sample data. Any suggestions why it is not calculating roll up for 2016 and work around?

Thanks

Re: line level count and avg at total is it possible?

sunny_talwar — Mon, 18 Dec 2017 17:08:12 GMT

Is this the goal?

Used this

Avg({<Period=,Period={'<=$(=max(Period))'}>}aggr(sum({<Period=,Period={'<=$(=max(Period))'}>}data),Yr))

Re: line level count and avg at total is it possible?

rmuhammad — Mon, 18 Dec 2017 18:01:25 GMT

Desired state is where total for year would be AVG, right now with this change in statement we have sum as total for year and overall is AVG but in separate column. If I get Goal is shown below is Ideal but even if I have 70/120 sum as totals and 95 (AVG) as overall total I would be happy and not spending time any further.

Re: line level count and avg at total is it possible?

sunny_talwar — Mon, 18 Dec 2017 18:32:01 GMT

This?

Sum({<Period=,Period={'<=$(=max(Period))'}>}aggr(sum({<Period=,Period={'<=$(=max(Period))'}>}data),Yr, mnth))/Count({<Period=,Period={'<=$(=max(Period))'}>}DISTINCT Yr)

Re: line level count and avg at total is it possible?

rmuhammad — Mon, 18 Dec 2017 20:36:53 GMT

THANK YOU!

I would have not figured this out with out your expertise.