Tuesday, May 12, 2020

FAQ Series - Weighted Averages

Lately there have been a lot of Forum questions about calculating weighted averages 

Mathematically, weighted average is defined as the sum of the individuals weights x the values divided by the total of the weights  

1 Table Calculation Example 

Posters are familiar with how to find weighted averages with a spreadsheet calculator 


But now with how it would be donw in Tableau - 

Start with a table calculation to get the percent of total weight :

 Weight Percent of total = SUM([Weight]) / TOTAL(SUM([Weight]))

then the row level weighted averages are:

  Weighted Average =  [Weight percent of total]*sum([Value])



At the row level the calculations are the same as the spreadsheet calculation 
If we add Grand Total - the totals are not correct?  



The solution is to include a formula that forces a value into the Grand Total line and the Wtd Avg in all other rows 

Size() = 1 in the Grand Total and Sub Total rows or columns


If SIZE()=1 then 

window_sum(sum([Value]*[Weight]))/window_sum(sum([Weight]))

else [Wtd Avg] end

and the result return the expected values:


The solution also works when another dimension is added 


set the table calculations to Pane Down 


Table calculations are last in the Order of Operations so when possible I use LOD's - I also think the LOD approach is easier to understand  - see the next 3 examples 

2 - Toy Store Example

Start with a toy store data set - 3 categories with 4 products in each - and determine the weighted average sales by category and overall



The weights are the number of units sold and the values are the dollars - 

The numerator can be calculated with an LOD:

{ FIXED [Category],[Item]: sum([Units])*sum([Dollars]) }

The denominator is 

:({ FIXED  [Category],[Item]:   sum([Units])})

the weighted average is:

sum([LOD Numerator])/sum([LOD Denominator])

by using LODs the viz can be displayed in detail (including the total and subtotals)



Or the hierarchy can be compressed to show the summary



3 Crime Rate Example

This example uses crime data broken out by region, state, and city to find the crime rate per 1000 population at the state and region level

The data is organized like this



The crime rate per 1000 at the city level can be expressed using Include LOD's - the Include statement at the city level will automatically accumulate the hierarchy is collapsed 

- Note also the numerator and the denominator are determined separately


Total crime =  sum({ INCLUDE [City]:sum([Nonviolent])+sum([Violent crime])})

Pop In Thousands = sum({ INCLUDE [City]: Round(sum([Population])/1000,1)  })

And State Level Wt Avg =   
{ FIXED [State]:sum([Total crime])/sum([Pop In Thousands])}

And at the City level      sum([Total crime])/sum([Pop In Thousands])


Display the results in map form using a viz in tooltip to display the crime rate at the city level as bars and the state level weighted average as reference lines 



Create a bar chart at the state/city level for city level crime rate and include a reference line for the state weighted average crime rate


and add a reference line to the columns



Finally on the map - place the detail sheet in the tooltip and filter for State



Hover over a city and the detail chart filtered at the state level opens in the tooltip




4 Weighted Average Interest Rate

A common use case is to determine the weighted average interest across a portfolio of loans






Given the face value of the loan an the interest rate the first step is find the interest in dollars for each loan

Interest Dollars   =    sum([Load Amount]*[Interest Rate])



the weighted average interest rate using an Include LOD (using include the rate will roll up as the hierarchy is collapsed

Weighted ave interest rate  =   

[Interest in dollars]/ 
sum({ INCLUDE [Loan Purpose], [ID number] : sum([Load Amount]) })



At the detail level the viz is simply


Collapsing the hierarchy to the loan purpose level and adding in the Grand Total returns the the line of business and total portfolio weighted averages 



 



I hope this help you understand how to calculate weighted averages - the workbooks containing the examples used here can be found at   




Enjoy
Jim




Sunday, April 12, 2020

FAQ Series - Creating Groups

Groups are a way to collect like items without creating an aggregate  - They can be static or dynamic, created manually, through a join or as the result of a calculation and spreadsheet users will find them as a useful replacement for a VLOOKUP -




Manual Grouping

Static groups can easily be created using the Create Groups functionality from the Dimension pane.  

Open the dimension and select "Create Group"




A window will open with several options on how to group members of the dimension

Search Option 

The search option can be used to select values with a common value or string - Use "Find All" then select "Group"




Then Rename the group for use later



Highlight and Select Option

Using the mouse, shift or ctrl keys highlight the members to be included then select Group



Add to existing group

When new members are to be added to an existing group use the highlight option and then "Add To" in the upper right corner and select the correct group from the dropdown




Create and Other Group

Any remaining members can be collected in an "Other" group using the check off box on the lower left




When done there will be a Dimension(group) will be added to the dimension pane which can be used alone or as part of a hierarchy





Create Group is easy to use for static (unchanging groups).  If the members of the groups are even infrequently changed other options may be a better choice

Use a Join to Create Groups

Often posters on the Forum want to recreate a Vlookup function from a spreadsheet calculator.  The approach uses a separate file that lists the dimension value and the corresponding group 

For example: Group customers alphabetically by the first letter of their last name.  

I created a simple excel file with the customer name (first and last) to match same dimension in our Tableau Superstore example, broke out the first and last name and then the first letter of the last name.





Then join to the Superstore data on Customer Name




and the Group has been added to the dimension pane




Grouping by joining a secondary file works when groups are changed infrequently or can be downloaded directly from another database.  I used a similar approach to grouping products into subcategories and categories along a product hierarchy taken from an ERP system.

Grouping using a Calculated field

When the grouping can be described using a calculation the groups can be even more dynamic.  The grouping by last name example can also be done using a calculation 


left( split([Customer Name],' ',2),1)




Grouping created by using a calculation are dynamic and change whenever the data are updated or changed

Regardless of how they are created, groups give you a way to categorize the dataset.  They can be used in hierarchies:





and can be filtered








or can be used in calculations




I hope this gives you more insights for groups and how they can be used 

The workbook and calculations used in the examples can be downloaded at :




Enjoy

Jim







Wednesday, March 25, 2020

3 Dimension Charts

I'm a fan of 3-dimensional charts. You may be also and are familiar with how some spreadsheet systems have an option for 3 - Bar or line charts.  I get it. Some business managers just see the story better that way and besides they are cool.

Well, Tableau doesn't have a standard 3-D chart type but with some prep work on the data and extending the data densification concepts used to plot curves it is possible to get close.





To start if you are not familiar with data densification, Ken Flerlage has done an outstanding job of presenting the topic in a straight forward and understandable way - see:          https://www.flerlagetwins.com/2019/05/intro-to-data-densification.html

To extend those concepts to 3 dimensions think about a cube rotated in space and projection the position of points on the cube back to the 2-D display 



Points on the cube are transferred back to the viewing plane using the cube's rotation around the horizontal and vertical axes.  There is some math involved and for those who really understand 3D projection, the approach is not a true prospective - just a transfer of point onto a 2D XY plane.

There are a series of calculations that are needed (I will include them here but a downloadable copy of the workbook will be on my Tableau Public site - and you can just copy them from the book)  Using Superstore dimensions fore Order Date and Sale and relating them to the X, Y and Z dimensions of the cube:

  • Month numbers 1-12 are the X cube axis
  • The Sum(Sales) in Thousands are on the Y cube axis 
  • The Years are the reference on the Z cube axis 2017 = 1, 2018 = 2 etc 


Data Structure

After duplicating, data restructuring is needed - In each year (Partition) the original data are sequenced low to high and the copy high to low:



The data are grouped by  Year(Order Date)  the Z value is just an way to increment the year along the Z axis 1 for 2017, 2 for 2018 etc - 

With Tableau Prep the copying, sequencing and restructure the data can be accomplished in a few steps


1- Bring 2 copies of the data - The original file needs no changes - Sequence is from Min X to Max X values

2 - In the Copy - the data Sequence numbering needs to be from the Max X value to Min X
  • Determine the Max Z value ( Here 4)  :  { FIXED :max([Z])}
  • Find the Total number of records : {{Fixed Year : Countd([Month(X)]}}

  • Renumber the "New Sequence"  : (([fix max z]+([Z]-1))*[Fixed LOD1])+(([Fixed LOD1]+1)-[month(x)])
3- Union the files and revise names and remove unneeded columns




4- In the final step convert all the Y values from the copy data set to 0 :

if [Table Names]="Table 1" then [Sales/1000 (Y)] else 0 end

Then output a file for Tableau - 


Transformation Formulas 


Set up 2 parameters for the rotation around the Horizontal (view X axis) and Vertical (view Y Axis)  -




Tableau uses radians, convert the parameter values to radians:

Vertical axis rotation : ([Vertical axis rotation (degrees)]/360)*2*pi()

Horizontal axis rotation  :   -([Horizontal axis rotation]/360)*2*pi()

Note the negative sign on the horizontal rotation  

The transformation of point to the 2D X dimension is  

-([Month(X)]*cos([vert axis radians])-(3*[Z])*sin([vert axis radians]))

and for the 2D Y dimension

-(cos([hor axis radians ])*[Sales/1000 (Y)]

-[Month(X)]*sin([vert axis radians])-([z axis gap]*[Z])*cos([vert axis radians]))

Note the Z parameter for the "Z axis gap" is a parameter to adjust the spacing in the final viz.

Create the Viz

The VIZ uses the 2D X and Y calculations on columns and rows - add Z to the detail marks card and Sequence to path - 

I include the axis rotation and Z Gap spacing parameters to allow the user to adjust the view


When creating the dashboard I used sheet swapping and added a "Change parameter" set to hover for navigation 


Would love to hear from you on this and see what you have done




Enjoy

Jim

Post splash image

FAQ Series - Weighted Averages