Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
tmcgrath
Contributor
Contributor

Count aggr distinct dimension conditionally

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?

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

2 Replies
sunny_talwar

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

tmcgrath
Contributor
Contributor
Author

Thanks! That works perfectly.