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

    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.