1 Reply Latest reply on Jul 16, 2019 3:47 PM by John Sarantos

# Create a donor pyramid, but account for all possible dollar ranges.

I am using version 10.5 desktop and server.

I need to create a donor pyramid.

The data file is revenue data, where we calculate the donor giving ranges when the data file is created. Image one is the easy part because it reflects the donors and their ranges from today's transactions, but, at this point in time, no-one has giving at the top range yet so it is missing from my chart.  If you look at image 2 below, that is a sample of what I want to see.  I want the top range to show up, even if there are no gifts at this point in time.

I thought I would be able to do this with groups but I can't figure out a way to do it.

Any help would be greatly appreciated.

Image 1 below

Image 2 below

• ###### 1. Re: Create a donor pyramid, but account for all possible dollar ranges.

Hi Nardirv,

The first thing that comes to mind to solve this problem is to use a data scaffold.  The idea is based on creating rows in your data set for each possible range.

For example, say right now your data is

 RowID Name Category Count 1 Joe Birds 5 2 Julie Bees 4 3 Jasper Baboons 3

If you want to have all Category dimensions available, you will need to build a scaffold with all the available Category values.

 Cat_Scaffold Birds Bees Baboons

After joining in the scaffold, your data will look like:

 RowID Name Category Cat_Scaffold Count 1 Joe Birds Birds 5 1 Joe Birds Bees 5 1 Joe Birds Baboons 5 2 Julie Bees Birds 4 2 Julie Bees Bees 4 3 Julie Bees Baboons 4 3 Jasper Baboons Birds 3 3 Jasper Baboons Bees 3 3 Jasper Baboons Baboons 3

As you can see, you data has ballooned in size, your original number of rows times the number of rows in the scaffold.  The benefit is now you have a Category dimension available for every row of your original data.

There are some good resources on scaffolding online:

This post on the forum links to several resources: Data Scaffolding in Tableau

There was also a video at TC18:  Solving tough time-based problems with skeleton tables and Tableau Prep - YouTube

To give you an example I used the Superstore data and approached the problem of wanting to be able to see every Sub-Category listed for each name, whether they purchased anything from that category or not.

If I didn't build a scaffold, this is what I would see for Aaron Bergman:

What I want to see is:

Step One: I created a simple table in excel that had all the Sub-Category values.

 Sub-Category Value Accessories 1 Appliances 1 Art 1 Binders 1 Bookcases 1 Chairs 1 Copiers 1 Envelopes 1 Fasteners 1 Furnishings 1 Labels 1 Machines 1 Paper 1 Phones 1 Storage 1 Supplies 1 Tables 1

Step Two: I added a connection to that data source

Step Three: I joined the Superstore data to the Scaffold data source as you see below.  (NOTE:  To create the 1 on the left-hand side, I first clicked 'Add new join clause', chose 'Create a join calculation' and then entered a 1 in the calculation field.):

Step Four: Go to Sheet 1 and Drag Customer Name to Rows

Step Five:  Drag Sub-Category (Sheet1) to my Rows.  So far so good, now I've got every Sub-Category represented for every Customer Name

Watch what happens if I drag Sales to the Text Marks card, tho:

This is not accurate information.  In order to see only the correct Sales figures now that I've added in my scaffold, I will need to create a Calculated Field.

Step Six:

Create a calculated field called True Sales that only returns the Sales figure if the Sub-category value of our original data set equals Sub-Category (Sheet1):

Step Seven:  Double-Click True Sales to add to the view

Step Eight:  Remove Sum(Sales) from the Measure Values card.

As you can see, we are left with just the accurate Sales information by Customer/Sub-Category, but we also still see all the possible Sub-Category values.