Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
SerSwagster
New Contributor II

Count(Avg) in a condition

Hi,

i want to do the count of the values whose average is bigger than a certain number (40). Moreover, the average is about values corresponding to a fixed parameter. The expression of the average works alone, but it doesn't work in the condition (average value > 40) and inside the Count operator too, because nested aggregation are not allowed. This is the expression:

Count(Avg({<[PollutantName]={Particulate?Matter?10} >} [Value]) > 40)

Any suggestion?

1 Solution

Accepted Solutions

Re: Count(Avg) in a condition

In that case, give this a shot

Sum(Aggr(
If(Avg({<[PollutantName] = {Particulate?Matter?10}>} [Value]) > 40, 1, 0)
, [PollutantName], Date))
6 Replies

Re: Count(Avg) in a condition

Which Dimension are you using when you are checking for Avg(Value) > 40? Try this

Count({<DimensionName = {"=Avg({<[PollutantName] = {Particulate?Matter?10}>} [Value]) > 40"}>} DimensionName)

So, basically replace DimensionName where you are checking the condition with the correct dimension name. Also, you might need to add a DISTINCT based on your requirement 

Count(DISTINCT {<DimensionName = {"=Avg({<[PollutantName] = {Particulate?Matter?10}>} [Value]) > 40"}>} DimensionName)
SerSwagster
New Contributor II

Re: Count(Avg) in a condition

Thanks for the answer.

My dataset (for an analysis about air pollution)  is composed by these fields:

MonitoringStationName - Region - Province - Date (decomposable into Year, Month and Date) - PollutantName - PollutantUnitOfMeasure - Value.

For each distinct value of Pollutant Name (they are only 7 so i can do it manually) i have to build a single paper with several charts.  Every chart is interactive, so selecting a particular time dimension or spatial dimension or both every chart changes consequently. The only chart I have problem with is a bar chart in which i have to show the number of instances in which the value exceeds the threshold. The X axis is filled by date and Y axis by the count of exceedances.

So, making a concrete en example, for the pollutant PM10 Particulate Matter the average value of a single day (i have hourly values) must be smaller than 40, otherwise the threshold is passed. So i have to make the count of exceedances for the specific pollutant type (with its specific metrics expression, like Avg > 40 ) according to the time selection and space selection made randomly operating on other charts (for example Count of exceedances in Rome in January of 2017, fixed the PollutantName as PM10 Particulate Matter).

I hope I have been clearer now.

Re: Count(Avg) in a condition

So, if I am reading this right, you want to compare this below average for each PollutantName and each Day... is that correct? For example if the value for the pollutant is over 40 for 2 of the 3 days, you want a count of 2?

Avg({<[PollutantName] = {Particulate?Matter?10}>} [Value]) > 40

 

SerSwagster
New Contributor II

Re: Count(Avg) in a condition

Yes , exactly, 2 because in 2 days the average value is over the limit. I want to calculate the number of days in which the threshold is exceeded, specifiying for each paper the specific pollutant name and formula (we are considering only Particulate 10 as PollutantName and 40 as threshold for semplicity, because other cases are analog).

So, if the selection made through other charts is of a single city and of a single day, the maximum value of count of exceedances must be 1 because day is our smallest granularity.

I think you are close to the solution.

Re: Count(Avg) in a condition

In that case, give this a shot

Sum(Aggr(
If(Avg({<[PollutantName] = {Particulate?Matter?10}>} [Value]) > 40, 1, 0)
, [PollutantName], Date))
SerSwagster
New Contributor II

Re: Count(Avg) in a condition

Thank you! This is exactly what i was searching for! 😎