Count aggr distinct dimension conditionally

Anonymous — Tue, 13 Dec 2016 18:04:32 GMT

I'd like to create an expression in a table that counts the number of Policies by Region that have a rating of 'Red' for Policy Health.

A Policy is classified as 'Red' if its Policy Health is >25. The only dimension in the table is Region.

In the same table, we calculate the Policy Health using the following calc:

sum({1*<Disabled = {'No'},[Run Date] = {'$(=max([Run Date]))'},[Firewall Type] = {'Border'}>}[Connection Count] * [SIM Score])
/sum({1*<Disabled = {'No'},[Run Date] = {'$(=max([Run Date]))'},[Firewall Type] = {'Border'}>}[Connection Count])

The above expression works fine. The below is what I've tried to count distinct 'Red' Policies per Region:

count(distinct if((sum({1*<Disabled = {'No'},[Run Date] = {'$(=max([Run Date]))'},[Firewall Type] = {'Border'}>}[Connection Count] * [SIM Score])
/sum({1*<Disabled = {'No'},[Run Date] = {'$(=max([Run Date]))'},[Firewall Type] = {'Border'}>}[Connection Count]))>25,Policy))

but no luck. I have also tried some aggr() functions but again can't crack this.

Any ideas?

Re: Count aggr distinct dimension conditionally

sunny_talwar — Tue, 13 Dec 2016 18:52:24 GMT

How about this:

Sum(Aggr(

If((Sum({1*<Disabled = {'No'}, [Run Date] = {'$(=Max([Run Date]))'}, [Firewall Type] = {'Border'}>} [Connection Count] * [SIM Score])/Sum({1*<Disabled = {'No'}, [Run Date] = {'$(=Max([Run Date]))'}, [Firewall Type] = {'Border'}>} [Connection Count])) > 25

, 1, 0), Policy, Region))

Re: Count aggr distinct dimension conditionally

Anonymous — Tue, 13 Dec 2016 18:56:05 GMT

Thanks! That works perfectly.

topic Re: Count aggr distinct dimension conditionally in QlikView

Count aggr distinct dimension conditionally

Re: Count aggr distinct dimension conditionally

Re: Count aggr distinct dimension conditionally