topic Re: Aggregation within the count function in QlikView

Aggregation within the count function

Tue, 01 Mar 2016 08:59:17 GMT

Hello friends

For every Date I want count KPI_names where avg KPI_value by KPI_name <1

i try this expression, but it doesnt work

= count ( {<aggr(avg(KPI_value), KPI_name) ={'<1'}>} distinct KPI_name)

Let me know if anyone has idea or decision of this.

Thank you in advance

Ruslan

Re: Aggregation within the count function

sunny_talwar — Tue, 01 Mar 2016 11:21:36 GMT

May be this?

=Sum(If(Aggr(Avg(KPI_value), KPI_name) <= 1, 1, 0))

or this:

=Sum(If(Aggr(NODISTINCT Avg(KPI_value), KPI_name) <= 1, 1, 0))

Re: Aggregation within the count function

tresB — Tue, 01 Mar 2016 11:27:10 GMT

May be this:

=count({<KPI_name={"=avg(KPI_value)<1"}>}DISTINCT KPI_name)

Re: Aggregation within the count function

Digvijay_Singh — Tue, 01 Mar 2016 11:56:00 GMT

This works, I was trying Count({<KPI_name={"$(=avg(KPI_value))<1"}>} Distinct KPI_name),

don't know why that dont't work,

Re: Aggregation within the count function

sunny_talwar — Tue, 01 Mar 2016 11:58:45 GMT

You don't need dollar sign expansion for search string such as Avg(KPI_value) < 1

Re: Aggregation within the count function

Thu, 03 Mar 2016 14:46:16 GMT

Hi, Sunny

It seems that the first expression works, but when i use it in pivot_table or in histogram i can see the values only for selected date or the last available date (14.02.2016) when the date is not selected.

Re: Aggregation within the count function

sunny_talwar — Thu, 03 Mar 2016 17:26:15 GMT

Try the other expression:

=Sum(If(Aggr(NODISTINCT Avg(KPI_value), KPI_name) <= 1, 1, 0))

Re: Aggregation within the count function

Fri, 04 Mar 2016 08:26:07 GMT

Sunny,

above expression sums KPIs that are less then 1 and show it at every bar in hystogram

Actually there are 2 KPIs < 1 at 07.02.2016 and 4 KPIs <1 at 14.02.2016

Re: Aggregation within the count function

jagan — Fri, 04 Mar 2016 08:54:45 GMT

HI,

You should not use $(), it evaluates the expression and replaces it

=Count({<KPI_name={"$(=avg(KPI_value))<1"}>} Distinct KPI_name)

The above expression becomes

=Count({<KPI_name={"Some Value"}>} Distinct KPI_name)

Hope this helps you.

Regards,

jagan.

Re: Aggregation within the count function

sunny_talwar — Fri, 04 Mar 2016 10:25:21 GMT

Try this:

=Sum(If(Aggr(Avg(KPI_value), KPI_name, Date) <= 1, 1, 0))

Re: Aggregation within the count function

Wed, 09 Mar 2016 06:40:57 GMT

Thanks Sunny for provided solution!