7 Replies Latest reply on Apr 10, 2018 10:00 AM by Sarah Zaleski

    count number of times something appears in a column

    Sarah  Zaleski

      Trying to count the number of times 1 appears in each column. total each column.  examples using WaitingProposal  (columns shown in sheet 2)

      calculating the number of times waitingProposal shows up in the column data with regards to the filters

      waitingProposal = 50 should be?

      These are the things I have tried: shown in sheet 3

      NumberIds = {FIXED [C Id (Workitem)]:COUNTD([C Id (Workitem)])}

      Count Weeks = Window_Count(Count([WaitingProposal]))

      NumberIdswp = {FIXED [C Status (Workitem)] : COUNTD([C Status (Workitem)] == 'waitingProposal')}

      Sumwp = SUM( INT ( [C Status (Workitem)] = "waitingProposal" ) )

      Total = TOTAL(COUNTD([WaitingProposal]))

       

      WaitingProposal = IF ( [C Status (Workitem)] == 'waitingProposal' AND [Max Update] = true ) THEN 1 ELSE 0 END /* used this to mark 1 or 0 for counting

       

      countwp = sum ( [WaitingProposal] )

      sumwptotal = Total(SUM ( INT ( [C Status (Workitem)] = "waitingProposal" ) ))

       

        • 1. Re: count number of times something appears in a column
          Michel Caissie

          Sarah,

           

          Since you want to count the distinct number of C Id that is classified by a specific logic,  and not the number of rows that is classified by that logic, I suggest that you take the following approach for the Waiting Proposal.

           

          Create a calculation returning the  C Id  (instead of 1 or 0 )

          IF  [C Status (Workitem)] == 'waitingProposal' AND [Max Update] = true  THEN [C Id (Workitem)] END

           

          and then aggregate this with a CNTD  and you will get the 50.

           

           

          Also this seems a bit overkill

          NumberIds = {FIXED [C Id (Workitem)]:COUNTD([C Id (Workitem)])}

           

          You can use either  only COUNTD([C Id (Workitem)])

          or if you need a fixed value, depending on the view, you can simplify with

          {FIXED [C Id (Workitem)]:MIN(1)}

           

           

          Be carefull here

          NumberIdswp = {FIXED [C Status (Workitem)] : COUNTD([C Status (Workitem)] == 'waitingProposal')}

          The  count of a dimension count 1 for every value. So the false would be counted as 1 also .

           

          Michel

          1 of 1 people found this helpful
          • 2. Re: count number of times something appears in a column
            Sarah  Zaleski

            Michel If I may ask you a few questions:

             

            I understand this now..thanks.....counts every one because fixed is performed before dimension filters ..right

            Be carefull here

            NumberIdswp = {FIXED [C Status (Workitem)] : COUNTD([C Status (Workitem)] == 'waitingProposal')}

            The  count of a dimension count 1 for every value. So the false would be counted as 1 also .

             

            I get the over kill message also after reading this article it helped also and you confirmed it.  thanks

            through my searches I found this discussion Re: Recruitment Dashboard - Getting open position count on specific dates

            which was the direction you sent me also.  thank you.

             

            the first if stmt i put into a variable for later use.  sheet4 proved to me they were correct

             

            I am trying to create a burn down chart with a weighting factor

            I hard coded the weighting factors at the moment

            1)they are not all there every time and

            2) not sure how to do that right now..

             

            here's what i did...

            WaitingProposal = IF [C Status (Workitem)] == 'waitingProposal' AND [Max Update] = true THEN [C Id (Workitem)] END

            I need this resulting value in a variable I think - see next sheet4

             

            sumwp = countd(waitingProposal)

            now that i can get a count of each status. I want to multiple it by a weighting factor then sum them all together for one value

            11 draft - 0*11 = 0

            10 waitingProposal -  50*10 = 500 sumwp = Countd(waitingProposal) CBL waitingProposal = [Sumwp] * 10

            9 waitingProposalReview - 0*9 = 0 sumwpr = Countd(waitingProposalReview) CBL waitingProposal = [Sumwpr] * 9

            8 waitinCode - 36*8 =              288 sumwc = Countd(waitingCode) CBL waitingCode = [Sumwc] * 8

            7 waitingcodereview - 17*7 =  119 sumwcr = Countd(waitingCodeReview) CBL waitingCodeReview = [Sumwcr] * 7

            6 waitingunittest - 14*6 =          84 sumwut = Countd(waitingUnitTest) CBL waitingUnitTest = [Sumwut] * 6

            5 sumwutr =                                95  Countd(waitingUnitTestReview) CBL waitingUnitTestReview = [Sumwutr] * 5

            4                                                16

            3                                                18

            2                                                10

            1                                                1

            0                                                0

                                                          1131 current back log = [CBL Draft] + [CBL waitingProposal]+[CBL waitingPropReview]+[CBL waitingCode]+[CBL waitingCodeReview]+ [CBL waitingUnitTest] + [CBL waitingUnitTestReview] + [CBL waitingSoftwareDescription] + [CBL waitingDescriptionReview]+[CBL waitingIntegration] + [CBL waitingVerification]+[CBL waitingValidation]+ [CBL waitingReleased]

            displayed on sheet5 but 1 off 1130

             

            would this be the right way to do this or is there a better way.

            Thanks Sarah

            • 3. Re: count number of times something appears in a column
              Michel Caissie

              First,  what I meant when using something like

              COUNTD([C Status (Workitem)] == 'waitingProposal')

               

              If you count or countd  a boolean dimension, it will  count 1 for all values whether they are true of false. The count() function  counts the non null values of a dimension.

              So instead of using something like;

              COUNTD([C Status (Workitem)] == 'waitingProposal')

              use something like

              SUM(if [C Status (Workitem)] == 'waitingProposal' then 1 else 0 end)

               

              Next, in CBL(waitingValidation) you have  [Sumwvalidation] * 0

              Here is the missing 1.  Remove the *0  and you will get 1131

               

              Finally, you can use the Grand Total column to display the Start log  on the same sheet.

              On sheet 5b(3) ,  set the Rows Total,  Analysis-Totals-Rows Total to left

              Edit Grand Total label for  Start Back Log

              And replace the calculation for;

              if first() = last() then [Start Back Log] else [Current Back Log] end

              first() = last() returns true only for the Grand Total, this way you can use  different values for the Grand Total columns  and the other columns.

              • 4. Re: count number of times something appears in a column
                Sarah  Zaleski

                I have went over this and now I am just confused.  When I removed my extract the db went to a crawl.  Only idea was to much crunching with the CNTD and MAX...

                 

                I never could get it to work nicely again so I started over.  Still same issue when I use similair methods we talked about.

                 

                I started a new tableau and wondered if you could point me in the right direction.  Any info would help.

                 

                I have code in the caption of sheet I explainging what I'm trying to do.

                 

                 

                Sheet1 is the over all data I want to use for processing about 2060 records

                 

                looking at AOI-451 I only want to count the last record with the max c_update date..

                 

                 

                Sarah L Zaleski

                Federal Aviation Administraion - Mike Monroney Aeronautical Center

                NAS Engineering - Software Engineering support

                Artic Slope Regional Corp

                405-954-9881

                • 5. Re: count number of times something appears in a column
                  Sarah  Zaleski

                  Mike,

                   

                   

                  What if I need to change MAX(C Updated) to a parameter end date? How would I count the C ID's then.

                   

                  count the number of times each status is marked with a 1 given an end date (meaning end date <= c updated)

                   

                  each c Id should end up with 1 row

                   

                   

                   

                  for example:

                   

                  if my end date is March 23, 2018 I want to mark the C updated row closes to that date but not over it. the one in blue

                   

                   

                   

                   

                  Somehow I'm thinking I need a calculated field to mark this row but I can't seem to figure it out.

                   

                   

                  Found this example but no luck on ideas

                   

                  https://community.tableau.com/thread/198900

                   

                   

                  Using the max(C Updated) marks the March 27, 2018 row

                   

                  Can you tell me how to mark the row above with regards to the end date(March 23, 2018) so I can later count the number of waitingProposal  statuses

                   

                   

                  Sarah L Zaleski

                  Federal Aviation Administraion - Mike Monroney Aeronautical Center

                  NAS Engineering - Software Engineering support

                  Artic Slope Regional Corp

                  405-954-9881

                  • 6. Re: count number of times something appears in a column
                    Michel Caissie

                    Sarah,

                     

                    I am quite busy these days, and I haven't got time to go through your latest workbook, but for your latest question , you can change the  Max Update for

                     

                    [C Updated (Workitem)] =

                    { FIXED [C Id (Workitem)], [C Modulename]: max(if DATETRUNC('day', [C Updated (Workitem)]) <= DATETRUNC('day', [End Date]) then [C Updated (Workitem)] end)}

                     

                    where End Date is the date parameter.

                    • 7. Re: count number of times something appears in a column
                      Sarah  Zaleski

                      Sorry to have bothered you.  been reading articles trying to make sense.

                       

                      Thank you

                       

                       

                      Sarah L Zaleski

                      Federal Aviation Administraion - Mike Monroney Aeronautical Center

                      NAS Engineering - Software Engineering support

                      Artic Slope Regional Corp

                      405-954-9881