3 Replies Latest reply on Nov 14, 2012 5:19 AM by Mark Holtz

    Dynamic Bin size

    Patrick Parsy

      I need to automatically adjust the bin size of a calculation depending of the (dimension) MeasureType.

      I have a calulated field (cal_binsize) giving me the desired bin size in the format of a CASE logic:

      CASE MeasurementType

        WHEN 'TypeA' then 0.5

        WHEN 'TypeB' then 0.1

        ELSE 1.0


      Now, how can I get the measure calulation axis to adjust automatically depending of the cal_binsize value?

      I have tried with a parameter, have not been able to do it apart from displaying the parameter selection on the worksheet and let the user select the value.  Another problem I noticed is that the list of value in that the bin selection does not seem to be dynamic even when using a parameter, meaning that if I add another bin size within my Case statement, I have to regenerate the bin selection list.

      Any ideas?





        • 1. Re: Dynamic Bin size
          Mark Holtz

          Hi Patrick,


          You could create a calculated fields to bin your data, using your bin size parameter:

          IF [Value] < 0*[Bin Size Parameter] THEN 0*[Bin Size Parameter]
          ELSEIF [Value] < 1*[Bin Size Parameter] THEN 1*[Bin Size Parameter]
          ELSEIF [Value] < 2*[Bin Size Parameter] THEN 2*[Bin Size Parameter]
          ELSEIF [Value] < 3*[Bin Size Parameter] THEN 3*[Bin Size Parameter]


          You'll have to make sure you add sufficient lines to cover your data aptly.

          2 of 2 people found this helpful
          • 2. Re: Dynamic Bin size
            Patrick Parsy

            Thanks Mark,  I experimented a bit with it, the solution seems to work, using the bin size calculated field (cal_binsize in my previous posting), but it looks like it introduces a performance issue... Good enough for now!

            • 3. Re: Dynamic Bin size
              Mark Holtz

              Yes, depending on the number of records you have, I could see performance tanking a bit. Even using extracts will not help you in this case because you have made a field that must re-calculate (for every record) any time the parameter is changed.


              Perhaps another solution is to create 2 separate calculated fields that both have hard-coded values, one for .05 increments and one for .10 increments.  Then, you could simply create one more calculated field that pulls in the .05 field when the .05 value is set in the parameter...


              [.05 bins] =
              IF [Value] < .05 THEN 0
              ELSEIF [Value] < .05 THEN .05

              ELSEIF [Value] < .10 THEN .10


              [.10 bins] =
              IF [Value] < .10 THEN 0
              ELSEIF [Value] < .20 THEN .10
              ELSEIF [Value] < .30 THEN .20


              [Swapping Field]
              IF [paramter] = .05 THEN [.05 bins] ELSE [.10 bins] END
              Then, you would put the swapping field on your view across the x-axis.