10 Replies Latest reply on Jun 3, 2016 3:43 PM by Ben Young

    Grouping Measures into sets

    Michael Silverberg

      Hi I am trying to group a blended and calculated measure 'Age' into different groups, but I cannot figure out how to do so.

       

      When I say 'groups' the end result should look like 0-7, 8-14. 14-30, 30-90, and 90+

       

      I tried creating a new calculated field with this code below, but have not been able to have success without making the worksheet overly complicated:

       

      If [Customer Age] <= 1

      Then "day 1"

      ELSEIF [Customer Age] >1 AND [Customer Age] <= 7

      THEN "> 1 day, < 1 Week"

      ELSEIF [Customer Age] >7 AND [Customer Age] <= 31

      THEN "> 1 Week, < 1 Month"

      ELSEIF [Customer Age] >31 AND [Customer Age] <=90

      THEN "> 1 Month, < 90 Days"

      ELSE "> 90 Days"

      END

       

      This is a sample of my data

      Screen Shot 2016-05-25 at 2.00.46 PM.png

       

       

      The age number is calculated from

      AVG([sqlDB)].[User Age]) - AVG([difference])

      Determines age of user when the record was created

       

      ([sqlDB)].[User Age]) is calculated from

      TODAY() - [Create Date]

      -determines the current age of user

       

      Difference is calculated from

      ( TODAY() - [Created Time] )

      Need to get subtract the gap between today

       

      [Create Date] = date the user was created

      [Created Time] = date the record that is being analyze was created

       

      How can I group these into sets to analyze in buckets? Much appreciated