0

countdistinct a column but its not working

Jazmine Indiana

July 25, 2016 08:10

None

Follow

 

I have this table and the formula for duration is:

the formula for the grouped and sorted is: 

when i used count distinct to count the instances of the multiple days(e.g. 1 day occurred 8 times, 2 days occurred 5 times) it wasn't displaying the correct count..

 

Help please :)

Thanx!

 

• Facebook
• Twitter
• LinkedIn

2 comments

• 
  Janice Janczyn July 25, 2016 16:42 Official comment

  Hi Jazmine,

  COUNTDISTINCT will return a count of each unique item passed into it and GROUP will return one of each unique item. For details, https://support.klipfolio.com/hc/en-us/articles/215547998-GROUP-GROUPBY-and-COUNTDISTINCT.

  Given your table:

     GROUP( 1, 1, 1, 2, 2, 2, 2, 2 ) returns 1, 2

    COUNTDISTINCT( 1, 1, 1, 2, 2, 2, 2, 2 ) returns 3, 5

  Your table contains 8 rows, but GROUP & COUNTDISTINCT will return only 2 items. To align your data and have a count show up for each row, use the LOOKUP function (https://support.klipfolio.com/hc/en-us/articles/216182777-LOOKUP):

     LOOKUP( DurationDays, GROUP( DurationDays), COUNTDISTINCT( DurationDays) )

  Thanks,

        Janice

• 0
  
  Omar Elmasry July 25, 2018 09:46

  Thanks Janice.

  I've been trying to do this for the past 3 days

  You're the best Janice.

  Thanks, 

  Andrew