12 Replies Latest reply on Oct 16, 2017 12:15 PM by Deepak Rai

# Count of a calculated field

Hello - i have a layout where i'm showing a campaign, class, and modules started, modules completed, and a module completion rate (started/completed).

An example of my output is as follows:

Campaign     Class    # of classes  Modules Started    Module Completed     Module Completion Rate

Test               Test1     3                    10                              10                              100.00

Test               Test2     3                     5                                 1                                   20

Test               Test3     3                    6                                   6                              100.00

# of classes is a Fixed Calculation that I create:

{ FIXED YEAR([activity_date]),[campaign_name]:COUNTD([class])}

What I'd like is to be able to have another column (classes with 100 % completion rate). So basically, a count of classes within each campaign where the completion rate is 100.00

So I'd like another worksheet that gives me this:

Campaign      # of classes  # of classes with 100 perc rate

Test               3                         2

Any tips on how to do this? Ive been stuck on this for the last few hours! Thanks

• ###### 1. Re: Count of a calculated field

{SUM(IF [Module Completion Rate]=1 THEN 1 ELSE 0 END)}

• ###### 2. Re: Count of a calculated field

You should be able to have something like: {FIXED [Campaign]: COUNTD(IF [Module Completion Rate] = 100 THEN [Class] END)}

• ###### 3. Re: Count of a calculated field

Hey Ben, i actually tried something like that earlier. The error is that it won't let me mix aggregate and non aggregate comparisions. (same goes for deepak's solution)

• ###### 4. Re: Count of a calculated field

IF [Module Completion Rate]=1 THEN 1 ELSE 0 END

• ###### 5. Re: Count of a calculated field

You may need to tell Tableau whether to do the table calc DOWN the table, or ACROSS the table, or along specified dimension(s).

Table Calcs are a good way to do aggregates of aggregates.

• ###### 6. Re: Count of a calculated field

And looking closer at your question, you probably need to do:

WINDOW_SUM ( IF [Module Completion Rate]=1 THEN 1 ELSE 0 END )

And I'll only say PROBABLY because I don't know all the intricacies of your workbook, and some other conditions might affect this.

Posting a sample workbook for questions like this usually eliminates the guesswork.

• ###### 7. Re: Count of a calculated field

Hey Joe - here's my workbook (lmk if you are unable to view it). Yep, I tried the windows sum earlier, and re-tried it again after your msg. If you look at Sheet 10, and specifically for Bearden High School, I get a row for each 'class'. all I want is to display 1 row for Bearden with the # of classes as 9, and Calculation 4 (which is # of classes with 100 % completion rate, as 2).

• ###### 8. Re: Count of a calculated field

You need this?

Thanks

Deepak

• ###### 9. Re: Count of a calculated field

that looks about right deepak. ill take a look at what you did! thanks and thanks to everyone else for their assistance

• ###### 10. Re: Count of a calculated field

Ok, If it Helps, then Please don't forget to Close thread by marking the Correct Answer as Helpful and Correct. If you need to take out 0 from Calculation 4 too, then just filter them out.you would get a cleaner view then.

Thanks

Deepak

1 of 1 people found this helpful
• ###### 11. Re: Count of a calculated field

Vikash -- The key to what Deepak did is found in the calc he has on the filter shelf.

You need all the classes on the sheet, else you can't get the individual calc by class.  The table calc generates the same value for each class within the school.  You only want to see one of those rows.  Deepak grabs the last one in each school.  (Look at the table calc settings for the filter.)  You can see that reflected in the tooltip on each row.  (Remove that from the tooltip so that you don't confuse the user.)

I usually grab the first one per "chunk", but it's all the same.

Nice job, Deepak.

• ###### 12. Re: Count of a calculated field

Thanks Joe.