8 Replies Latest reply on Mar 14, 2017 3:30 AM by Sreekanth Kasaraneni

    How to get countif of excel in tableau

    arvind jain

      Hello Gyus,

       

      I have a data I want to the same result in tableau.

      Bold entries of Name column are getting repeated in each month as in first month we have to take these in count but not in second or third month etc... for throughout the column entries. irrespective of closed date.

       

       

                                                                                                                                                                                 

      Closed DateMonthName
      1/1/20161HIL00053-3rd Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis &   Recommendation-Single Site Verification (SSV)
      1/1/20161HIL00177-3rd Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis &   Recommendation-Single Site Verification (SSV)
      1/1/20161HIL00188-3rd Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis &   Recommendation-Single Site Verification (SSV)
      1/1/20161HIL03303-3rd Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis &   Recommendation-Single Site Verification (SSV)
      1/1/20161HIL03304-3rd Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis &   Recommendation-Single Site Verification (SSV)
      1/1/20161CAL00051-3rd Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis &   Recommendation-Single Site Verification (SSV)
      1/1/20161CAL00367-3rd Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis &   Recommendation-Single Site Verification (SSV)
      1/1/20161CAL02030-3rd Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis &   Recommendation-Single Site Verification (SSV)
      2/1/20162CAL00051-3rd Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis &   Recommendation-Single Site Verification (SSV)
      2/1/20162CAL00367-3rd Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis &   Recommendation-Single Site Verification (SSV)
      2/1/20162CAL02030-3rd Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis &   Recommendation-Single Site Verification (SSV)
      2/1/20162HIL00053-4th Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis &   Recommendation-Single Site Verification (SSV)
      2/1/20162HIL00177-4th Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis &   Recommendation-Single Site Verification (SSV)
      2/1/20162HIL00188-4th Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis &   Recommendation-Single Site Verification (SSV)
      2/1/20162HIL03303-4th Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis &   Recommendation-Single Site Verification (SSV)
      2/1/20162HIL03304-4th Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis &   Recommendation-Single Site Verification (SSV)
      2/1/20162ALL01053-2nd Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis &   Recommendation-Single Site Verification (SSV)
      2/1/20162ALL01064-2nd Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis &   Recommendation-Single Site Verification (SSV)
      2/1/20162ALL01124-2nd Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis &   Recommendation-Single Site Verification (SSV)
      3/1/20163CAL00367-3rd Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis &   Recommendation-Single Site Verification (SSV)
      3/1/20163CAL02030-3rd Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis &   Recommendation-Single Site Verification (SSV)

          

      My end goal to have this data in tableau

       

                         

      #MonthSite touchesFTR
      188
      2118
      320

       

      Please help me

       

      Message was edited by: arvind jain

        • 1. Re: How to get countif of excel in tableau
          Sreekanth Kasaraneni

          Hi Arvind,

           

          Please find the attached workbook.

           

          below calculations are used in the workbook.

           

           

          output

           

           

          Tried to achieve your output.please let me know if there is wrong in my calculations.

           

           

          Thanks,

          Sreekanth.

          • 2. Re: How to get countif of excel in tableau
            arvind jain

            Shreekant... did before is in excel sheet calculation field. my actual data does not contain it.

             

            I am just showing you how I did in excel.

             

            I want to replicate same in tableau

            • 3. Re: How to get countif of excel in tableau
              arvind jain

              I have only three column that is closeddate,month, Name. This only contain my actual data

              • 4. Re: How to get countif of excel in tableau
                arvind jain

                I have removed "did before" column from my actual data as that was a calculated field in excel sheet.

                • 5. Re: How to get countif of excel in tableau
                  Sreekanth Kasaraneni

                  Hi Arvind,

                   

                  Create a calculated field with the below calculation (Did before Count)

                   

                   

                  IF [Month] = 1 THEN { FIXED [Name],[Month] : COUNT([Name])}

                   

                   

                  ELSEIF [Month] = 2 THEN {FIXED [Name]:COUNT( if [Month]>=1 AND [Month]<=2 THEN [Name] END)}

                   

                   

                  ELSEIF  [Month] = 3 THEN { FIXED [Name] : COUNT([Name])}

                   

                   

                  END

                   

                   

                   

                   

                   

                  final output

                   

                   

                  please find the attached workbook for reference.

                   

                  Thanks,

                  Sreekanth.

                  1 of 1 people found this helpful
                  • 6. Re: How to get countif of excel in tableau
                    arvind jain

                    Thanks shreekanth... it help me

                    • 7. Re: How to get countif of excel in tableau
                      arvind jain

                      Hi

                      ,

                       

                      I am facing one more issue

                      what if i have a duplicate value in the first month itself.

                      and instead of doing the month vise calculation like below

                      IF [Month] = 1 THEN { FIXED [Name],[Month] : COUNT([Name])}

                      ELSEIF [Month] = 2 THEN {FIXED [Name]:COUNT( if [Month]>=1 AND [Month]<=2 THEN [Name] END)}

                      ELSEIF  [Month] = 3 THEN { FIXED [Name] : COUNT([Name])}

                      END

                       

                      I want to check the name from the first row till last row. check for duplicate. here did before is the calculated field that I need to generate.

                       

                         

                      Closed DateMonthNamedid before
                      1/1/20161HIL00053-3rd Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis & Recommendation-Single Site Verification (SSV)1
                      1/1/20161HIL00177-3rd Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis & Recommendation-Single Site Verification (SSV)1
                      1/1/20161HIL00188-3rd Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis & Recommendation-Single Site Verification (SSV)1
                      1/1/20161HIL03303-3rd Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis & Recommendation-Single Site Verification (SSV)1
                      1/1/20161HIL03304-3rd Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis & Recommendation-Single Site Verification (SSV)1
                      1/1/20161CAL00051-3rd Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis & Recommendation-Single Site Verification (SSV)1
                      1/1/20161CAL00051-3rd Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis & Recommendation-Single Site Verification (SSV)2
                      1/1/20161CAL00367-3rd Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis & Recommendation-Single Site Verification (SSV)1
                      1/1/20161CAL02030-3rd Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis & Recommendation-Single Site Verification (SSV)1
                      2/1/20162CAL00051-3rd Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis & Recommendation-Single Site Verification (SSV)3
                      2/1/20162CAL00367-3rd Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis & Recommendation-Single Site Verification (SSV)2
                      2/1/20162CAL02030-3rd Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis & Recommendation-Single Site Verification (SSV)2
                      2/1/20162HIL00053-4th Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis & Recommendation-Single Site Verification (SSV)1
                      2/1/20162HIL00177-4th Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis & Recommendation-Single Site Verification (SSV)1
                      2/1/20162HIL00188-4th Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis & Recommendation-Single Site Verification (SSV)1
                      2/1/20162HIL03303-4th Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis & Recommendation-Single Site Verification (SSV)1
                      2/1/20162HIL03304-4th Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis & Recommendation-Single Site Verification (SSV)1
                      2/1/20162ALL01053-2nd Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis & Recommendation-Single Site Verification (SSV)1
                      2/1/20162ALL01064-2nd Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis & Recommendation-Single Site Verification (SSV)1
                      2/1/20162ALL01124-2nd Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis & Recommendation-Single Site Verification (SSV)1
                      3/1/20163CAL00367-3rd Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis & Recommendation-Single Site Verification (SSV)3
                      3/1/20163CAL02030-3rd Carrier-RF Tuning (Drive-Based): SSV/Cluster Analysis & Recommendation-Single Site Verification (SSV)3
                      • 8. Re: How to get countif of excel in tableau
                        Sreekanth Kasaraneni

                        Hi Arvind,

                         

                        try below calculation.

                         

                        IF [Month] = 1 THEN {FIXED [Month],[Name] : COUNTD([Name])}

                        ELSEIF [Month] = 2 THEN {FIXED [Name]:COUNT( if [Month]>=1 AND [Month]<=2 THEN [Name] END)}

                        ELSEIF  [Month] = 3 THEN { FIXED [Name] : COUNT([Name])}

                        END

                         

                        Thanks,

                        Sreekanth.