2 Replies Latest reply on Feb 19, 2014 9:06 AM by Tyler Scheid

    Calculating the ratio of values within a dimension.

    Tyler Scheid

      Hello-

       

      I'm trying to calculate winegrape budbreak % to plot on a graph, based on a dimension called 'Item' which has 2 measures of interest (Dormant Bud Count, Green Bud Count) at given dates and locations.

       

      For example:  on a given date and location 20 green buds counted, 80 dormant buds counted; I'm trying to produce a calculation that results in 20% for that date and location to plot on a graph.

       

      The challenge is that the 'Item' dimension has many different measure types (cluster count, shoot length, berry size, etc), and '0' measures come across in the database as Null.

       

      My approach is to create calcs that isolate each measure, and to convert nulls to 0 values:

      'Green Bud Count' = IFNULL(IF [Item]='Green Buds' Then [NumberValue] end, 0)

      'Dormant Bud Count' = IFNULL(IF [Item]='Dormant Buds' Then [NumberValue] end, 0)

       

      Then I use these calculations in a third calc called 'Bud Break %' = [Green Bud Count]/([Green Bud Count]+[Dormant Bud Count])

       

      This all seems logical, and should be pretty straightforward, but the resulting calc isn't returning an expected value.  I get either 0 or 100%.  Workbook attached so you can see what I'm doing.........and obviously doing wrong!

       

      Help would be greatly appreciated!

       

      Thanks,
      Tyler