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.

    . Nardirv

      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.
          John Sarantos

          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

           

          RowIDNameCategoryCount
          1JoeBirds5
          2JulieBees4
          3JasperBaboons3

           

           

          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:

           

           

          RowIDNameCategoryCat_ScaffoldCount
          1JoeBirdsBirds5
          1JoeBirdsBees5
          1JoeBirdsBaboons5
          2JulieBeesBirds4
          2JulieBeesBees4
          3JulieBeesBaboons4
          3JasperBaboonsBirds3
          3JasperBaboonsBees3
          3JasperBaboonsBaboons3

           

          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-CategoryValue
          Accessories1
          Appliances1
          Art1
          Binders1
          Bookcases1
          Chairs1
          Copiers1
          Envelopes1
          Fasteners1
          Furnishings1
          Labels1
          Machines1
          Paper1
          Phones1
          Storage1
          Supplies1
          Tables1

           

           

          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.

           

          Please let me know if this was helpful!

           

          Thank you,

          John