1. The 100% Stacked Bar Chart is the one where each segment is a percentage, which adds up to 100%. 

    Someone asked me recently how to sort the bars according to a segment's percentage. I thought it must be easy: Just google it. Then I tried myself and didn't find a satisfactory reference. So I decided to write one. In the process I did get inspirations from these two sources: Sorting a stacked bar chart using a parameter and a solution by Zhouyi Zhang in the Tableau's community site. Here I am going to present two solutions: using LOD (Level of Details) and Table Calculations.

    LOD vs Table Calculations

    Both are good for implementing most of Tableau designs. One or the other, depends on our choice. The main difference is:
    • LOD: It's context sensitive. We need to use context filters in the chart if necessary.
    • Table Calculations: Regular filters work well. We have to set computing directions correctly.

    LOD solution

    The key is to create a parameter [Para Segment] based on Segment and a parameterized segment percentage [Para Sales %] using LOD. With LOD, we ensure that this value can be used in Sort function. Note that we use ZN() to get 0% when the segment in question doesn't exist.

    Then sort [Sub Category] vertically and Segment horizontally by the same field of [Para Sales %]:
    Note that one sort is ascending and the other is descending. Here is the result:

    Table Calculations Solution

    Tableau often allows more than one solutions to one problem. Here I am going to show another solution based on table calculations.

    First let's create a calculated field for the selected segment's percentage. Make sure to set the computation along Segment! Use ZN() to get 0%.

    With a negative sign as a discrete value, this can be used as an ordering item by being placed at the leftmost position on Rows. Hide the header afterwards. BTW this is a sorting technique when the value is derived from table calculations. We can't use it in Sort function because of table calculations.

    Last but not the least, sort Segment by Selected Sales ascendingly. Horizontally, this will keep the selected segment always on the left.
    Voila, both LOD and table calculations methods will give us the same result. We can sort the 100% stacked bar chart by a segment's percentage.
    Feel free to download the demo workbook. Leave comments if questions.

    Posted by
    0

    Add a comment

  2. Kan(view)ban(board) has been used as a visual process management tool by Toyota since 1940s. Nowadays, it is widely adopted beyond manufacturing industry, especially in agile software development.

    I first used Kanban board on Jira platform from Atlassian. I never thought I would need to design it in Tableau because it doesn't have many visual elements. However, I have been part of a few dashboard designs for project management. We can add visual elements using Tableau to make Kanban look better.

    Recently someone came up to me asking how to design a Kanban board in Tableau. Here is my answer to it. Hope it's helpful.

    Data Model

    The key to Kanban data model is a history of task status changes. Each record in the history must include: Task, Status, UpdateTime, TaskID and maybe other attributes.

    This data model will also help analyze details like time spent per task, # of tasks completed per week etc, to gain more insights into the workflow, if we wish.

    Board Design

    - Index() on Rows


    The key in designing the Kanban board, is placing the discrete Index() on Rows, computing along TaskID which is in Details. This way, all the task tiles will be stacked up tightly. Then uncheck "Show Header".

    - Record Filter

    This filter is designed to keep only tasks of the latest status.

    - Show Empty Columns

    Assuming that we have a history of data where each status appeared at lease once, we can use this technique. This will give us a fixed grid even when no task is in a status. Otherwise a status column may be missing when no task is in that status, and the grid is varying.

    We can design Kanban board of various styles. Below are a few simple examples which can be downloaded here. Based on these, you can build more sophisticated Kanban if you wish. But, it might defeat the purpose of Kanban.

    Caveats

    - Remove Done tasks

    Done tasks should not stay on the board forever. There needs a condition by which the tasks be taken off the board. For example we can design a filter that keeps the Done tasks of the latest 3 days.

    - ColumnWise Design for a Fixed Grid

    If we only use a snapshot of the historical data, where some statuses may be missing from time to time. for example, if there is no blocked task, the Blocked column will not show. The board grid will be changed. The column wise design will fix this problem by keeping a stable grid, no matter whether tasks of some statuses are available or not. Basically, it means one status column per sheet. We need 5 sheets for this Kanban.

    Posted by
    4

    View comments

  3. To have multiple bars and lines with categories in the same worksheet has been hard with Tableau. This request has come up from time to time. Dual axis won't cut it. With the new layering technology, it becomes possible. Inspired by Tableau Zen Master Adam McCann's exemplary Zoomable dashboard, I created this little quick-and-dirty demo dashboard laying lines and bars over each other.

    Here are the two geo type fields that are created:
    One trick is to create a hardcoded index for categories, because an aggregated Index() won't work in the formula.
    A parameter Spacing is used to fine-tune the space between the bars.

    When designing map layer based charts,  we need to turn off the background map.
    The workbook can be download here.


    Posted by
    0

    Add a comment

  4. This is about a particular kind of data blending.

    How many approaches of data blending do you know? 

    Never mind. I know 3 approaches to blend them. There may be more. Tell me if you know any.

    My understanding of data blending is: More than one data source is being used in the worksheet. Whenever I see an orange label on a data source or pill, some kind of blending is taking place.

    Here are the 3 that I know. Note that the first two are based on relationships. The 3rd is not.

    1.Dimension-based blending

    This is the most common one. It's like a left join, but it is not quite. Joining takes place at row level. Blending is at dimension level. It is based on dimension linking or creating relationship between two dimensions in either of the data sources. The secondary data source only provides attributes to the primary data source, which is in aggregation based on the linking dimension. No new dimension will come from a secondary data source.

    2.Filter from a secondary data source

    We can apply a dimension filter from one data source to another, as long as the other data source has the same field with exactly the same name, such as Date, Country etc. So a filter may come from a secondary data source.

    3.Reference from a secondary data source

    Without any dimension linking or even without any common dimension or any relationship, we can always drag a field from a secondary data source and drop it in the worksheet in view. This happens when we need to reference a metric in that secondary data source, such as Max Date, Average Sales etc. We can use these metrics in calculations or in reference lines etc.

    A curious message always pops up saying you can't reference the measure in another data source without relationship. This is not true. We can. We don't need a relationship to use that value.

    Here is an example. We need to visualize the sales from California and Delaware. The data are in two separate sources.

    To align both in date range and in vertical scale, we referenced 3 metrics from the California data set: (Uncheck all the linking dimensions first. Otherwise the Max/Min values may be different.)

    • Max order date
    • Min order date
    • Max monthly sales amount
    We created 3 reference lines in the Delaware sales chart. Hide them in real case. Download the workbook to view the details.
    In some earlier posts 1 2, we also described techniques using the third data blending approach.

    Voila, the emphasis of the post is about the third data blending technique. Hope it helps. Leave messages in the comment area if you have questions.

    Posted by
    2

    View comments

Blog Archive
Blog Archive
Loading
Dynamic Views theme. Powered by Blogger. Report Abuse.