7 Replies Latest reply on Mar 22, 2015 5:58 AM by nagaraju.meda

    Using an IIF statement calculated field and its subsequent use in a ratio calculation...

    Brent Malak

      I'm using a dimension's "status" in an IIF statement to summarize volumes based on the "status" value.


      Later, I'm using this calculated value as the numerator in another calculation to generate a ratio/percentage value.  But the calculation is not calculating to anything but 100%.


      My first calculation is

      Accepted (#): IIF([Status]='Accepted',[Claims (#)],0 )


      The measure [Claims (#)] is one of the base fields in my database.  The [Status] dimension may vary as "Accepted" or "Rejected".


      The second calculation is

      Accepted (%#) = [Accepted (#)]/[Claims (#)]


      This should be a ratio of the accepted claims (a subset, defined in the calculation above), divided by the entire set of claims.


      I've tried using various incarnations of each formula with sum() functions, but get errors that aggregate and non-aggregate metrics cannot be mixed.


      I've attached sequential screenshots, too.


      Thanks for any suggestions/tips/solutions.