topic Re: issue with aggregation in QlikView

issue with aggregation

Wed, 25 Nov 2015 18:18:06 GMT

Hi everyone,

I need to know for what Year the maximum number of calls are received from Active type over the years in a textbox.(Excluded the year filter so i have put "Year=")

I am using the following expression.

=max(Aggr(Count({<TYPE={'Active'},Year=>} Call_ID ),Reason,Year)).

With this my result in 29813.

Individually for Year

2015 - 29813,

2014-32994,

2013- 27661,

2012 - 13343,

2011-7232

when i do the above expression the result should be 32994.

What is wrong with the expression??

Re: issue with aggregation

sunny_talwar — Wed, 25 Nov 2015 18:26:03 GMT

May be you need a DISTINCT count?

=Max(Aggr(Count(DISTINCT{<TYPE={'Active'},Year=>} Call_ID ),Reason,Year)).

Re: issue with aggregation

Wed, 25 Nov 2015 18:29:23 GMT

Nope, didn't work.

I have selected 2015, But for some reason year filter is not being Excluded.

Re: issue with aggregation

sunny_talwar — Wed, 25 Nov 2015 19:23:58 GMT

Would you be able to share a sample?

Re: issue with aggregation

Wed, 25 Nov 2015 19:31:23 GMT

I can't share nothing more than this as data is confidential.

I need to retrieve the highest value of all the charts using an Aggr() in a textbox.

Re: issue with aggregation

effinty2112 — Wed, 25 Nov 2015 19:40:22 GMT

Hi,

Why do you need to aggregate on the field 'Reason'? If you take it out to leave

=max(Aggr(Count({<TYPE={'Active'},Year=>} Call_ID ),Year))

what is the answer then?

In 2014 if you have 29813 Active type call of reason A and 3181 for reason B (total 32994) your Aggr expression will return 29813.

Re: issue with aggregation

Wed, 25 Nov 2015 19:44:21 GMT

Hi Andrew,

Please refer to the above image. i need Highest number of calls for any reason over all the years. it is working fine if i don't select an year. but if i select an year it is limiting to the that Year.

That is the challenge am facing.

Re: issue with aggregation

effinty2112 — Wed, 25 Nov 2015 20:38:50 GMT

Hi Nithin,

Try this:

=max({1<Year=>}Aggr(Count({1< COVERAGE_TYPE={'Active'}>}Call_Log_ID),CallReason,Year))

double check the syntax in the editor because I've not been able to.

Cheers

Re: issue with aggregation

Mon, 30 Nov 2015 14:07:10 GMT

Thanks Andrew, It worked.

Re: issue with aggregation

effinty2112 — Mon, 30 Nov 2015 19:46:13 GMT

You're very welcome!