Aggregation within the count function

[#1]

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

[sunny_talwar]

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))

[tresesco]

May be this:

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

[Digvijay_Singh]

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

don't know why that dont't work,

[sunny_talwar]

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

[#6]

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.

[sunny_talwar]

Try the other expression:

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

[#8]

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

[jagan]

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.

[sunny_talwar · Accepted solution]

Try this:

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