Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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

7 Replies
MVP
MVP

Re: Counting Sites within a banding

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

Re: Counting Sites within a banding

try this

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

MVP
MVP

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.

This is what I read.

MVP
MVP

Re: Counting Sites within a banding

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

Re: Counting Sites within a banding

hahahaha ok

Not applicable

Re: Counting Sites within a banding

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