6 Replies Latest reply on Mar 23, 2012 11:37 AM by Odwani Thomas

# Create table calculation from within a scope

I am trying to generate what I thought would be a simple bar chart that shows the number of students with 1-2, 3-4, 5-9, and 10+ unexcused absences for a period of time. The table that I am pulling from has a list of unexcused absence instances with a corresponding School, Student ID Number, and Date. I am able to produce a list of all the student IDs and their calculated number of absences, but this is based on a table calculation (COUNT(Number of Records)) and I am not sure how to use these values to generate my desired chart. Am I going about this wrong?

In the attached File:

• Table of Data
• The format of Data that I would be getting
• Step 1
• What I am able to produce using (COUNT(Number of Records))
• Desired Outcome
• Two sample charts to show what I am trying to get at
• ###### 1. Re: Create table calculation from within a scope

Hi Odwani,

I was able to download and create a viz with the buckets you defined with a Caclulated Dimension.  I created the calculation based on Number of Absenses as follows named "Buckets":

IF [# of Absences] >9 THEN "10+"

ELSEIF [# of Absences] >4 THEN "5-9"

ELSEIF [# of Absences] >2 THEN "3-4"

ELSE "1-2"

END

I was then able to use this in my Columns to create the desired chart.  I did not solve this with the Tableau Bins functionality because of the varying size and naming of your buckets.  My TWBX starting with the Step 1 Data is Attached.  Hope this helps.

-David

• ###### 2. Re: Create table calculation from within a scope

I guess the main problem that I was having was that the number of absences was going based on the lowest level, where in the second worksheet it was based on the student number. But in the desired sheet the lowest level was the school, which would just be a sum of the total absences for that school, not the number of students with those corresponding numbers of absences

• ###### 3. Re: Create table calculation from within a scope

Odwani,

I made a couple of quick changes to the workbook by adding some names to my worksheets to give more context. I also added a sheet the sums the number of students (count of records) by bucket by school.  The first worksheet sums the number of absences by bucket by school.  Find updated workbook attached.

-David

• ###### 4. Re: Create table calculation from within a scope

I am able to produce Step 1 in my workbook however in order to get to what is in your example I would need to export this to excel and then re-import so that these I can use the "# of Absences field" in calculating the desired outcome. Because this data will need to be updated every few days, I am trying to avoid this option. I am trying to get to the third worksheet (Desired Outcome Worksheet) while using the initial data that I am getting (Table of Data worksheet)

• ###### 5. Re: Create table calculation from within a scope

I have created a TWBX that creates the desired end visualization from the base data-set.  I had to create a 'Number of Absences' field and then refactor the 'Buckets' function.  Take a look.

• ###### 6. Re: Create table calculation from within a scope

I think that did it!! Thanks!!