2 Replies Latest reply on Apr 19, 2019 11:51 AM by Jim Dehner

    Create Distinct Count with date range

    San M

      Hello All,

       

      I need help in creating a Calculation where i can get a distinct count based of the empl start date and by their different year's performance rating. The Issue i am facing: When i select the start date range and then put the performance year filter in the Row column, it is not showing me the accurate information. Below is the screenshot for your reference. The Count section is showing the right information but the start date calculation field is not accurate.

       

       

      The formula that i created to get to Start Date Calculation Field is below which is not showing the right results because when i add the 2nd line to the formula, it is not showing the correct results to FY18 rating column because i think the way i created the formula it is taking the empl. who started after Oct 2016 as well in the FY18 count. When i just leave the formula with only COUNTD(IF ([Start Date] > #04/01/2016# AND [Start Date] < #10/02/2016#) then [Employee ID] , then it shows the right count in FY18 column

       

      COUNTD(IF ([Start Date] > #04/01/2016# AND [Start Date] < #10/02/2016#) then [Employee ID]

      elseif ([Start Date] >#04/01/2016# and [Start Date] <#04/02/2017#) then [Employee ID] end)

       

      What is the best formula i can use that will reflect the unique count by each review year in a single cross-tab format?

       

      Thanks

      San

        • 1. Re: Create Distinct Count with date range
          lei.chen.0

          Hello San,

           

          Please provide a sample workbook or some sample data.

           

          Calculation differs according to the data structure.

           

          For example, how many date fields do you have?

           

          If one date field,

          [Employee ID]     [Date]

          -> {FIXED [Employee ID]: MIN([Date)} is necessary to calculate the start date.

           

          If two date fields,

          [Employee ID]     [Start Date]     [Performance Date]

          -> this will be simpler

           

           

          Regards

          Lei

          • 2. Re: Create Distinct Count with date range
            Jim Dehner

            Hi

            happy friday

            I see yo attached excel files - so that data is not sensitive - our role is to help you get past problems you are having with your tableau analysis

            Please attach your TWBX workbook with the data so we can see your progress and where you ran into problems

             

            thanks

            Jim