1 Reply Latest reply on Jul 19, 2018 1:53 PM by Joe Oppelt

    Setting a filter in a calculation

    Scott Schmeling

      Is there a way to do a where statement in a calculation. 

       

      I want to create a static field that states.

       

      Sum (Enrollments)/ Sum(Referrals) Where Date between A and B

       

      Then another static field that states

      Sum(Enrollments)/Sum(Referrals) Where Date between B and C

       

      I can't use window Calcs.  I can't use Table Calcs.  I want these to be two static fields not based on anything else.  because the final out put will have both of those as well as another item with a different date range A - F for a sparkline.  The should be able to appear on the same row in a table just different columns.

       

      Basically I want to do the calculation then turn the item into a Dimension- not a measure.  This is just one several that will go into a much larger formula to display categories.

       

      I'm really trying to find a way to simplify the issue  as I can't put a Work Book up due to HIPPA law. 

       

      Thank you,

        • 1. Re: Setting a filter in a calculation
          Joe Oppelt

          Are you looking to get those two sums across all data in the data source?

           

          If so use LODs:

           

          { FIXED : SUM( if [Date] > [A] and [Date] < [B] then [Enrollments] END) }

           

          this will become a calc field on every row.  You can even make it a dimension if you wanted.

           

          If you want to do that sum at the level of some dimension(s) then do:

           

           

          { FIXED [Dim1](, [Dim2], ...) : SUM( if [Date] > [A] and [Date] < [B] then [Enrollments] END) }

           

          It will still be a value on every row, but will change with each new dimension value (or combination of values if multiple dimensions.)

           

          Do that for each measure.

           

          You can nest LODs, or you can create separate calcs and then do [calc1]/calc2].