topic Re: Count by aggregation in QlikView

Count by aggregation

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

Hi Experts,

Can any one please help me on below requirement in the UI can't modify the back-end script.

In the below sample source file i need to calculate the Count of Red Ind in the text box with the expression like count of Red Ind aggregated by Metric Group and Metrics for all the Months.

Requirement with expected output:

By default, it should total red count for all months =10 based on unique Metric Group and Metrics

if user select

June month then it will show >> 2 (as red count is repeated for Met 12 two time need to pick only 2 rows out of 3 rows in June month)

>> July -- 3

>> Aug -- 1

>> Sep -- 1

>> Oct --1

>> Nov -- 2

Please find the attched sample soure file Please provide the solution in the app.

Thanks in advance.

Re: Count by aggregation

Ralf_Heukäufer — Sat, 18 Nov 2017 18:30:21 GMT

Hello,

I don´t understand exactly how you come to a count of 10 and for august of a count of 1.

Would you count for a combination of metric group and metrics?

Then you can create a extra field in your load script like:

"Metric Group"&' '&Metrics as MandM,

And use

count(aggr([Red Ind],MandM))

to count it

In the other case you can use:

count(aggr(aggr("Red Ind",Metrics),[Metric Group]))

to aggregate both of them

Re: Count by aggregation

sunny_talwar — Sat, 18 Nov 2017 23:00:46 GMT

May be this (again I don't understand why Aug = 1 and total is 10)... I would have thought the total to be 11 and Aug = 2

Sum(Aggr(Count(DISTINCT Metrics), MonthYear))