3 Replies Latest reply on Nov 21, 2018 7:18 PM by mursid rahman

    How to add multiple calculated measures

    Simone Harris

      I need to create 3 separate calculated measures and add them together but am having difficulty.

       

      I'm trying to calculate Active IDs + New IDs to show the total no of IDs.

      First I need to filter on the valid IDs.

       

      PROBLEM 1 - Calculating Active IDs:

      I am currently using the following to calculate the Total no. of records in a view :

       

      WINDOW_SUM(SUM([Number of Records]))

       

      but using a filter (e.g. see below) and would like to create a calculated measure that will provide this same total number without needing to filter:

       

      e.g I am filtering on ID using a Wildcard to Exclude Matched values that 'Does not start with' X

      then using WINDOW_SUM(SUM([Number of Records])) to calculate the total no. of records with this filter in place.

       

      What formula can I use to calculate this instead?

       

      PROBLEM 2 - Calculate Expiring IDs:

      Similarly, I need to then create a separate calculated field for a separate calculation as follows :

       

      e.g I am filtering on ID using a Wildcard to Exclude Matched values that 'Does not start with' X

      Plus another filter on Year of Exit Date

       

      then using WINDOW_SUM(SUM([Number of Records])) to calculate the total no. of records with this filter in place.

       

       

      PROBLEM 3 - Calculating New IDs:

      Similarly, I need to then create a separate calculated field for a separate calculation as follows :

       

      e.g.

      I am filtering on ID using a Wildcard on Matched values that 'Starts with' X

      Plus another filter on Year of Targte Start Date

       

       

       

      then I'm using WINDOW_SUM(SUM([Number of Records])) to calculate the total no. of records with both filters in place.

       

       

       

       

      PROBLEM 4 - :

       

      Now I need to add all of the calculated fields together tog display the no. of total net IDs:

       

      [Active IDs] - [Expiring IDs] + [New IDs] = [Total No. of Net IDs]

       

      I presume I need to create a separate calculated field to do that as well.

       

      I'd appreciate any help and advice to create the 4 calculated fields as currently I'm having to do this manually to get the total and I've struggled for too long trying to work this out!

       

      Thank you.