Counting Sites within a banding

[#1]

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

[sunny_talwar]

How about something like this:

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

[Kushal_Chawda]

try this

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

[sunny_talwar]

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

[Kushal_Chawda]

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

This is what I read.

[sunny_talwar]

Haan bhai, upar pad, I fixed my response to you

[Kushal_Chawda]

hahahaha ok

[#8]

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