9 Replies Latest reply on Apr 27, 2018 1:44 PM by Jim Dehner

# Aggregate Calculated Field Question

Hi!

I created a calculated field earlier today that is simply SUM([Successful Classes]) / COUNT([Enrolled Classes)] to find the overall success rate for students. I would like to though create a visual comparing development student success rates vs the entire student population....OR the student population sans development students.

My goal was to create two metrics....

Metric 1:

If [Development Student] = 'Yes' then SUM([Successful Classes]) / Count([Enrolled Classes)] end

Metric 2:

If [Development Student] = 'No' then SUM([Successful Classes]) / Count([Enrolled Classes)] end

Unfortunately both are bringing up the same error; Syntax error (maybe you are missing an operator)

Just wondering what part of code I am missing to make this work.

Also, on a semi-related note; is it possible to create a visual where I could use the aggregated success rate but break it out by Dev students in a line graph including other metrics?

• ###### 1. Re: Aggregate Calculated Field Question

Hi

the issue is a misplace )

If [Development Student] = 'Yes' then SUM([Successful Classes]) / Count([Enrolled Classes)] end

should be in each statement

If [Development Student] = 'Yes' then SUM([Successful Classes]) / Count([Enrolled Classes]) end

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Aggregate Calculated Field Question

It appears to have not. Now the error coming up is "cannot mix aggregate and non aggregate comparisons or results on "if statements"

Any idea what I can do?

Thanks,

Bryan

• ###### 3. Re: Aggregate Calculated Field Question

sorry

in addition to the first issue there was a second - [development student ] needs an aggregation - attr()

If   attr (   [Development Student]   )  = 'Yes' then SUM([Successful Classes]) / Count([Enrolled Classes]) end

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 4. Re: Aggregate Calculated Field Question

Not your fault! So it appears to have worked.....BUT (there's always a 'but') none of the values are showing up! Here are two snapshots of the work. I am sorry for the hassle but thank you for the help!

Bryan

• ###### 5. Re: Aggregate Calculated Field Question

I would actually like to see the data for [successful classes] and [enrolled classes]

The typical cause of no values showing up is Null values in the data - to get around that you can try wrapping the dimensions in ZN()

If   attr (   [Development Student]   )  = 'Yes' then SUM(  zn([Successful Classes])) / Count( zn([Enrolled Classes])) end

I have never used zn() with count so you may get an error - try with and without

also in your formula are you are counting all the classes and not the distinct classes - that may be what you want to do but if not then use countd()

Jim

• ###### 6. Re: Aggregate Calculated Field Question

Here you go.

Feel free to give some constructive criticism on the dashboards and other visuals. And any ideas for what I should also try to show.

I am relatively new to Tableau and this is my first big time project for work. I need to make the best impression imaginable.

Thanks again!

• ###### 7. Re: Aggregate Calculated Field Question

OK

see the attached

there is good news and bad news here

first the good news - is this what you wanted?

first the change to the dev stud success rate

then the more difficult part - you have an indicator in the data set - Development Student Population - that is a 0 or a 1

and you are using it as a way to split the data set into 2 parts -

as long as you only use a single part in the viz (eg 0 or 1)

you can do use a filter to sort out the other part and get the Development rate

but if you include the 0 this is what you get

the usual way to do this is to duplicate the data set and use a Union between the 2 data sets - it creates 2 tables and you use one to do the "0" calculations and the other to do the "1" calculations

I'm hesitant to suggest that route unless needed because you have 3 data sources and a lot of sheets here and I don't know how much will be affected

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 8. Re: Aggregate Calculated Field Question

The reason why I was trying to do it the other way was for ease of use. I would like to be able to have a graph where I can look at the success rate for dev students and the success rate for all other students and potentially een loop in the success rate for all students in comparison. Right now, like you alluded to, I can create a filter based on whether the student is a dev student or not. While that is all well and good for a number of things I will be showing, I would prefer to be able to show them both at the same time without worrying about filtering.

• ###### 9. Re: Aggregate Calculated Field Question

see the attached

I did an example - I don't know how this will play with the other sheets -

just to show you where we are going here is the text table output

It filters you can select dev3 and dvev31 - i'll explain and yes you can change those

you start with creating a union of the daata with a  itself

on the Data source tab just drag your sheet till you see the red bog drop to junio then drop

the data in now copied into 2 table and there are 2 new columns - one identifies the source data sheet and the other uniquely identifies the tables and dev3 and dev3.1 in this case

Now when you go to a worksheet you have a dimension that you use to separate the same data for 2 different purposes

in the example I'm not certain all the math and logic around your internal metrics makes sense - but it will serve as an example

I used dev31 and the data set for Dev stud population =1 and dev3 and the set for dev stud pop =0

First recalculate the successful class and the total call values as

then the success rate is a simple division

That is how you can do it - but it may have messed up other things - your workbook is very complex

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful