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

# count number of times something appears in a column

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

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

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

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

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

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

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

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

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