7 Replies Latest reply: May 17, 2016 6:43 AM by Chris Waterworth

# Counting Sites within a banding

Hi Qlik Com,

This is a bit of a complex question so I'll try and phrase it best I can but please comment if you need more data.

I have a series of sites that I banded by population eg 0-100, 100-250 etc. I have then counted the number of sites in each band and put it to a table.

We measure the performance of each site by the follwoing equation =sum(Output)/population.

The output is summed as we have multiple delievries from each site.

We have a target we are working towards across all sites.

I need to count the number of sites in each band that are above the target.

The problem I'm having is that when entering the formulas the sum(output) is summing all the sites within a band and giving an average rather than counting the individual sites.

I am trying the following two formulas unsuccessfully:

count({\$<(sum(Output)/Pop)={">=\$(=[Target]}>}distinct([Site Name]))

if(sum(Output)/Pop)>[Target],(count(distinct([Source Name]))),0)

[Target] is a variable that works in a KPI and is constant.

Any help would be a lifesaver I am working to a deadline.

Cheers,

Chris

• ###### Re: Counting Sites within a banding

Count(DISTINCT {\$<[Site Name] = {"=Sum(Output)/Pop >= Target"}>} [Site Name])

UPDATED based on the fact that Target is a variable and not a field

Count(DISTINCT {\$<[Site Name] = {"=Sum(Output)/Pop >= \$(Target)"}>} [Site Name])

But since you mentioned it is a constant, then without the Dollar sign expansion might also work

Count(DISTINCT {\$<[Site Name] = {"=Sum(Output)/Pop >= Target"}>} [Site Name])

Hahahaha sorry for confusing, but I guess try all three expression (2 here and 1 provided by Kush) and see which one works

• ###### Re: Counting Sites within a banding

Sunny T you are my Hero, worked like a charm.

• ###### Re: Counting Sites within a banding

try this

=count({\$<[Site Name]={"=(sum(Output)/Pop)>=\$(=[Target])"}>} distinct [Site Name])

• ###### Re: Counting Sites within a banding

Is Target a field or a variable? Not sure I understood this from the post. If it is a variable, then you solution might be right

My bad, it is clearly stated that it is a variable

• ###### Re: Counting Sites within a banding

[Target] is a variable that works in a KPI and is constant.