2 Replies Latest reply: Sep 20, 2012 10:07 AM by tmm050276 RSS

    Having Difficulty Excluding Dimensions in Set Analysis

      I have looked through Numerous examples but I cannot get this to work.

       

       

      I current have the following Dimesions:

       

      Carrier

      Family Id

      Mem Id

      Service Date

      Mem Paid

      Plan Paid

       

      I then have to calculate Plan Paid for  Service Dates that are greater than an eligibility date and am trying to use a set analysis to sum it to a Member Level

       

      sum

      (
      {$<  [Service Date]={">$(=max(total <[Carrier],[Mem Id]> [Eligibility Date]))"},
         [Service Date] = , [Plan Paid] = , [Mem Paid]>}  [Plan Paid]

       

       

      But this does not sum at the Mem Id level, it is suming each individual line.  I have tried adding aggrs and totals but cannot seem to get this to work.  Any ideas?

       

      thanks.

        • Re: Having Difficulty Excluding Dimensions in Set Analysis
          Stefan Wühl

          First, I think you shouldn't use [Service Date] twice in your set modifier list (what do you want to achieve herewith?).

           

          If you want to aggregate up to a certain level of your dimensions (i.e. dimensionality() ), you could use a total with a field list (similar to what you used in the advanced search for Service Date set modifier).

           

          So maybe like

           

          =sum( total<Carrier, [Family Id], [Mem Id]> {$<[Service Date]={">$(=max([Eligibility Date]))"}>} [Plan Paid])

           

          You see, I am also not sure about the advanced search in the [Service Date] set modifier. The dollar sign expansion and the set expression is evaluated once per chart, not per dimension value. Do you need to calculate the max Eligibility Date per current Carrier and Mem Id dimension value?

           

          Maybe some sample lines of data together with your expected outcome help to understand your requirements.

           

          Regards,

          Stefan