1. A friend of mine asked me how to create a bar chart in the style of Tableau Prep. Another important requirement is: It has to be in one sheet.
    This is actually a series of bar charts with each in its own column. Theoretically this can be done on a dashboard with multiple sheets. That would be a bit tedious. Given that the data is in a single data source and the columns are just different categories, this makes things a little simpler.

    I used the superstore data set to give it a go. Here is what I got.
    The key to create this is using the Index() over Sub-category dimension on Row shelf. Place the Sub-category in cards such as Details or Label or Color.

    I created option 1. Then she customized it exactly in the Tableau Prep style. I recreated it as option 2 below using Superstore data set:
    Voila, this shows how we can create multiple bar charts in one sheet. The workbook can be downloaded here.

    Custom Sort

    Note that the sorting is done using the custom sort within the tableau calculations of Index(). The custom sort allows various sorting based on a field, such alphabetic or decreasing sales amount.

    Null Values

    In case of null values in Sum(Sales), the ordering may be in disorder. we need to use the following settings for Index(). Check both dimensions and move the column dimension to the top. Restart indexing from every Category. This setting actually works no matter when Sum(Sales) has nulls or not. The custom sort is the same as above.

    Rank as Index

    An alternative is replacing Index() by Rank(Sum(Sales)) if we want to sort by sales amount. Using Ranking as index seems a more robust solution. Nulls may interfere with the result sometimes. Try using Rank(ZN(Sum(Sales))) to fix the issue.



    Posted by
    0

    Add a comment

  2. Coloring does make a difference, big or small depending on cases.

    I noticed Yang Yu of Shanghai made a great viz and won Viz of the Day a couple of days ago. It required a lot of work to get the data set pulled from Open Street Map. It looks like this:
    This is the Tableau rendition of Geoff Boeing's original research
    https://geoffboeing.com/2019/09/urban-street-network-orientation/

    I found it a bit monotonic. So I decided to add color to it. Then, I found that among the 4 quadrants, the other 3 are almost the rotated replica of the first one. Thus we just need to color the first quadrant. There are 9 equal-size slices per quadrant, one per 10 degree. So we only need a palette of 9 colors for the coloring. In my design, I assigned 9 colors in a color spectrum sequential order.

    Here is the result:

    Besides being very colorful, the most revealing thing is, I see cities having the same street orientations. Two big cities in Australia, both Melbourne and Sidney have the similar street orientation. Is this a miracle, or they have the same urban planner? Manhattan is alone in its street orientations which is unusual.

    Other insights include that most US cities are well urban planned.

    Color does help a lot in identifying patterns of similar orientations.

    Feel free to download my workbook.
    Posted by
    Labels: #TweakThursday
    0

    Add a comment

  3. In one of the viz designs, we are using a data set that already includes the running sum, which is precalculated to save us some computations.

    When calculating the totals per quarter or per year, we just need the numbers in the last month of the period, namely the number in the last month of the quarter, or the numbers in the last month of the year. This requires special calculations for both subtotals and grand totals.
    Tableau Zen Master Jonathon Drummey has written profoundly on this topic in 2012 which served as my source of inspirations. In his blog, he showed how to manipulate the subtotals and grand totals.

    In his examples, he showed how to use right most dimension for the custom calculation of both subtotals and grand totals. The aggregation result in his example is fairly simple. In our case, we found it not enough to give correct results for both totals.

    In our solution, we used all the dimensions and derived a formula that allows to calculate subtotals and grand totals differently.
    Note that we used both Month and Quarter Min()/Max() to set conditions. For quarterly subtotals, we used
    • Min(Month) != Max(Month) and Min(Quarter) = Max(Quarter)
    For yearly totals, we used
    • Min(Month) != Max(Month) and Min(Quarter) != Max(Quarter)
    In case necessary, subtotals and grand totals can be calculated using different formulas. Note that formula can be further expanded to accommodate more dimensions.

    The workbook for the above solution can be found here.

    PS.
    Note that the subtotals and grand totals seem to work using any linear aggregations. But there are cases where complex aggregations may not work such as ratio SUM()/SUM(). You will see AGG() as aggregation function in Tableau.



    Posted by
    Labels: CrossTab
    4

    View comments

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