2 Replies Latest reply on Mar 7, 2016 11:34 AM by Michael Lance

    Aggregate for < x% Market Share

    Michael Lance

      Hello,

       

      I am looking at hospital market shares of patients given the attached workbook and the following data structure:

        

      HospitalPatientsAreaPlanYear
      A - JJ0 - 999 1 or 2AAA or BBB2011 through 2013

       

      Note: I use "Total Market Share" and "Market Share" interchangeably below.

       

      I can get the market share per hospital for a given year (or selection of years) and plan (or selection of plans) via this calculated field ([Total Market Share]:

      { INCLUDE [Hospital]:

      SUM(@@

      {FIXED [Area],[Hospital],[Year]:

      SUM(IF [Plan]<>"Unintelligible" AND [Hospital]<>'NA' THEN [Patients] end)}

      )}

      /

      { EXCLUDE [Hospital]:

      SUM(

      { FIXED [Area],[Year]:

      SUM(IF [Plan]<>"Unintelligible" AND [Hospital]<>'NA' THEN [Patients] end)}

      )}

       

      But here's the rub:

      Let's say I want to take all hospitals with a market share of <6% and group them together, treating them as one "other" hospital?

      I came up with this ([<6% TMS]):

      IF attr([Total Market Share])<.06 THEN "Other (<6% TMS)" ELSE ATTR([Hospital]) END

       

      The trouble is, when I create a line graph like so, it displays one dot per hospital and year where I want one dot across hospitals per year (essentially, one line like with all the other hospitals):

      I even tried:

      IF {INCLUDE [Hospital]:SUM([Total Market Share])}<0.01 THEN

      //then sum across hospitals falling into this category

      {FIXED [Hospital]:SUM([Total Market Share])}

      ELSE {INCLUDE [Hospital]:SUM([Total Market Share])}

      END

       

      ...but alas, I was beaten back into submission as this did not help.

       

      Any ideas?

       

      My only guess is that I need to write a custom SQL group by statement that would create market share fields per plan.

       

      Thanks in advance,

      Michael

        • 1. Re: Aggregate for < x% Market Share
          Simon Runc

          hi Michael,

           

          Have you tried creating the [Total Market Share] calculation as a purely FIXED LoD? The advantage of using FIXED is that the results are returned at Row Level (completely 'Off Canvas'), which means you can then access this calculation as a 'real' dimension. This way you no longer need Hospital in the VizLoD...

           

          So I've created the Market Share as

          [Total Market Share - FIXED LoD]

          {FIXED [Area],[Hospital],[Year]:

          SUM(IF [Plan]<>"Unintelligible" AND [Hospital]<>'NA' THEN [Patients] end)}

          /

          { FIXED [Area],[Year]:

          SUM(IF [Plan]<>"Unintelligible" AND [Hospital]<>'NA' THEN [Patients] end)}

           

          and then created you >6% off this to create a 'real' dimension

          [<6% TMS - From FIXED]

          IF [Total Market Share - FIXED LoD]<.06 THEN "Other (<6% TMS)" ELSE [Hospital] END

           

          I can then use these in this Dim in the Viz and no longer need Hospital in the VizLoD to make it all work

           

           

          Now there are draw backs to this...firstly if you want any filters to affect the results, you'll need to make them 'in context' (this pushes the filter up the pipeline before the FIXED LoD is calculated), or you can add the dimension to the FIXED LoD (as we have with year, for example...and so get a value for each year). The other draw-back (and you'll only notice this on large/complicated data-sets) is a performance cost to using FIXED LoDs.

           

          Hope this helps.

          • 2. Re: Aggregate for < x% Market Share
            Michael Lance

            So helpful!

             

            Thanks,

            Michael