3 Replies Latest reply: Dec 9, 2011 11:57 AM by PeterJ.Andrews RSS

    Set Analysis Function inside a Dimension

      Hello All,

       

      I am trying to incorperate a cumulative frequency up to the data sets median within a chart dimension (The data set is below). The ideal outcome is that the function will return the total number people that waited less than the median for each month.  The function I am using is

       

      =count({<[Wait]={'<$(=Floor(Median([Wait],1))'}>} ID)

       

      This works fine when a month and service has been selected but when there are no selections the '<$(=Floor(Median([Wait],1))' is equal to the waits for the whole of the dataset.

       

      Is there any way to stop this and force the median calculated for each dimension.

       

       

       

      Load * Inline[

      Service, Month, ID, Wait
      A Service, Jul_2011, 2048042, 1
      A Service, Aug_2011, 1659531, 4
      A Service, Aug_2011, 2096983, 5
      A Service, April_2011, 677073, 7
      A Service, Aug_2011, 2052211, 7
      A Service, Aug_2011, 2143493, 7
      A Service, Jul_2011, 2174455, 7
      A Service, Jul_2011, 2048042, 1
      B Service, Jul_2011, 2048042, 1
      ]

       

      Thanks for your help

       

      Peter

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

        • Set Analysis Function inside a Dimension
          Vlad Gutkovsky

          Peter,

           

          What you are experiencing is an inherent restriction with set analysis. Set analysis is calculated once per chart, and does not understand what dimension it's currently in. You might want to use "total" with a "<>" (subtotal) qualifier, in combination with some conditional function(s), to achieve your results.

           

          Regards,

          Vlad

          • Set Analysis Function inside a Dimension
            Stefan Wühl

            Peter,

             

            since your conditional function will probably involve the median function, an aggregation function, I think you will need advanced aggregation here, maybe like this:

             

            =count( aggr( if([Wait]< Floor(Median(total<Month> [Wait]),1), ID), Month,ID,Service))

             

            Regards,

            Stefan

              • Set Analysis Function inside a Dimension

                Thanks for the responses. Swuehl - I just tweeked it a little bit by including Service into the total <>

                 

                The final calculation that works out cumulative frequency to the median used for finding medians for grouped data within a service and month dimension is:-

                 

                =count(aggr( if([Wait]< Floor(Median(total<Month,[Service] > [Wait]),1), [ID]), Month,[Id],[Service]))

                 

                Regards

                Peter