0

Using GROUPBY with multiple measures

Kevin Burke

July 09, 2019 20:48

None

Follow

Hi - I wasn't able to find anything online to help me with grouping data using multiple measures

My Klip and formula is below

I'm simply looking to sum more than just "DISPENSED" , there are other values like DISPENSED SUN and DISPENSED READER that I am trying to sum in this column

Thanks!

• Facebook
• Twitter
• LinkedIn

5 comments

• 0
  
  Janice Janczyn July 10, 2019 16:15

  Hi Kevin,

  I can't see all your data, but I'll assume column A is the list of Clinic Names. Use the CONTAINS function to check for any items in column K that contain the word "DISPENSED" and Applied Actions to group and aggregate your data:

  1. Clinic Name column, set formula to @A:A and on the Properties tab, select Group repeating labels.
  2. Glasses Dispensed column, set formula to  IF( CONTAINS( @K:K, "DISPENSED"), 1, 0 ) and on the Properties tab, set Aggregation to Sum.

  I'm not sure how your Lives Served column is calculated so haven't included it here, but similar logic would apply.

  Thank you,
                Janice

• 0
  
  Kevin Burke July 10, 2019 16:20

  Hi Janice,

  Thanks for the quick response.

  Is there a way I can sum more than just "DISPENSED" in Column K? 

  I am looking to sum more than one value such as

  DISPENSED

  DISPENSED SUN

  DISPENSED ROC

  Do I input more CONTAINS formulas inside the IF function? 

   

  Thanks again,

  Kevin 

• 0
  
  Janice Janczyn July 10, 2019 20:22

  Hi Kevin,

  CONTAINS( @K:K, "DISPENSED") will return true for each item in column K that contains the word "DISPENSED" (e.g., DISPENSED, DISPENSED SUN, DISPENSED ROC) otherwise will return FALSE, so you only need 1 CONTAINS in the IF function.

  Thanks,
            Janice

• 0
  
  Kevin Burke July 11, 2019 12:55

  Okay sorry bad example

  What if I need to return DISPENSED and something like SENT TO LAB

  How can I sum different words like that ?

• 0
  
  Janice Janczyn July 15, 2019 12:16

  Hi Kevin,

  You can use any combination of our logic functions in your IF condition. For example, OR( @K:K = "DISPENSED", @K:K = "SENT TO LAB") will return true if at least one of the conditions returns true. If you have further questions, please email support@klipfolio.com.

  Thank you,
               Janice