Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
try this
=count({$<[Site Name]={"=(sum(Output)/Pop)>=$(=[Target])"}>} distinct [Site Name])
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
[Target] is a variable that works in a KPI and is constant.
This is what I read.
Haan bhai, upar pad, I fixed my response to you
hahahaha ok
Sunny T you are my Hero, worked like a charm.