4 Replies Latest reply: Jan 6, 2017 10:52 AM by Erryn Leask RSS

    Can I add row number to aggregate function in charts - non-standard quartile request

    Erryn Leask

      Hi all,

       

      I will try and keep this as simple as possible:

       

      Aim:

      1) The client wants us to break the data into quartiles, BUT based on literally sorting all Staff IDs ascending by salary and then dividing into 4 equal sections (e.g if 100 employees then split into 4 groups of 25). Ignoring the median.

      2) It should then be possible to run quartiles on different selections as per filters (e.g see quartiles for different pay elements as well as gross pay)

       

      The Problem:

      1) We CANNOT use Fractile() as there are e.g 40 employees all on the same pay and so the Fractile function would group all of them into one bucket, thus making the 4 groups uneven. The client wants the buckets completely even even if it means splitting the same pay value into 2 or more quartiles.

      2) We CANNOT do it in the loading script (or as far as we've tried) as I am unable to make the script flexible enough to be able to filter on various pay elements.

       

      What we've tried/Hoping to achieve:

      1)  Have tried to look at using variables in the load script as well as fractile in front end but as per 'The Problem' above none of these worked

      2) Considered using Rank within the aggr() function but again it pulls the same problem as Fractile() since all matching values will be given the same rank rather than unique numbers as per RowNo()

      3) Have also tried num(Rank(Amount, 4)) which was given as a solution to another question here but it is producing some funny outputs when I tried to make a test table (e.g all numbers a unique but they are certainly not in asc/desc order) NOTE: Amount is sorted ASC.

       

      Capture.PNG

      Theoretical solution:

      1) What I think I need to do is in the table chart - create an aggregate table (flexible enough to change based on filters) and then apply RowNo() to that aggregated table and use something along the lines of:

       

      =if(RowNo() <= (count(distinct([Staff Number])/4), 'Quartile A',

                if(RowNo() <= ((count(distinct([Staff Number])/4) *2), 'Quartile B'...and so on and so forth for the 4 group (and of course within the aggr() function)

       

      2) If anyone knows how to fix the Rank() problem above that may also provide the solution we need

       

      Thanks in advance!!!