14 Replies Latest reply on Sep 28, 2019 9:55 PM by Soumitra Godbole

    Waterfall

    Agnieszka Ludzik

      Hi,

       

      I would like to create a waterfall showing totals for selected start date & end date and difference between those 2 periods by legal entity (please see picture below).

       

      Parameter: Start Date

      Parameter: End Date

       

      Colour legend:

      green - positive values

      red - negative values

       

       

      Example of data structure:

      Have you got any idea?

      Thank you in advance.

        • 1. Re: Waterfall
          Bryce Larsen

          Please see this tutorial from Tableau and see if it helps: How to create a waterfall chart with a second dimension on color in Tableau - YouTube

          • 2. Re: Waterfall
            Agnieszka Ludzik

            Unfortunately, it does not help.

             

            In this case I have to generate total for End Date (2017).

             

            I know how to create 2016 bar and Entity breakdown part, showing difference between End Date and Start Date. But how to show 2017 total?

             

             

            Thanks.

            • 3. Re: Waterfall
              Bryce Larsen

              Ah, I understand now. Apologies. Hmm. Tricky one. This is how close I got so far:

              Tricky to figure out how to have "Total" be running sum across the entire way. How many "segments" do you anticipate having? Is it a definitive amount?

              • 4. Re: Waterfall
                Agnieszka Ludzik

                @

                 

                1 of 1 people found this helpful
                • 5. Re: Waterfall
                  Soumitra Godbole

                  Hi Agnieszka,

                   

                        No worries, and thanks for your sincere feedback. Anyway, here is a new and interesting solution

                  I have, while trying to think out of the box and not using the commonly listed or explained methods.

                   

                  I have enclosed the simple dummy data file (in the format that you displayed) except your example

                  was from superstore sales, while I created my values by merely looking at the Waterfall chart image

                  you provided just to get an idea of the overall look.

                   

                  Dashboard.png

                   

                  After being bogged down by traditional methods (Tableau), and with a little bit of imagination and

                  creativity I decided to experiment with 2 Dual Axes for both X and Y axis. Fortunately it worked and

                  here is your solution. Do let me know if you have any questions or need further clarity. Best Wishes !

                   

                  Sincerely,

                   

                  Soumitra

                  • 6. Re: Waterfall
                    Agnieszka Ludzik

                    @ ,

                    Thanks for your time and innovative solution!

                     

                    but unfortunately it's the details that matter :-)

                     

                    Some of my data have a bit different structure:

                    - year - it is combination of year and quarter

                    - entity is a string without any number

                     

                    and on the X axis I need to have original entity name, not Entity 1,2,3,4....

                     

                    and the entity number is not fixed, sometimes we can have 4 entities, sometimes 12 based on selected period (Start Date and End Date are selected on parameters).

                     

                     

                    EntitySalesYear
                    Entity  A2,52016Q4
                    Entity B4,02016Q4
                    Entity C1,02016Q4
                    Entity D3,52016Q4
                    Entity  A4,22017Q1
                    Entity B0,82017Q1
                    Entity C4,82017Q1
                    Entity D4,22017Q1

                    .

                    Any idea how to accomodate these data?

                    Thanks.

                    • 7. Re: Waterfall
                      Agnieszka Ludzik

                      how could I modify X Axis A (Sales) calculation?

                      Now it is: 0.25 +([Max Entity Index]+0.5)*([Year]-2016)

                      but my structure of Year is a combination of year and quarter, e.g. 2017Q1, and I compare it to e.g. 2016 Q4.

                       

                      Also I do not have legal entity names: Entity A, Entity B, but e.g. 'Home Office', 'Customer', 'Consumer'.

                       

                      And now waterfall does not work.

                       

                       

                      Thanks.

                      • 8. Re: Waterfall
                        Soumitra Godbole

                        Hi Agnieszka,

                         

                            Finally here is the enhanced solution I mentioned about earlier. It has added Start & End Date

                        Parameters. In addition to this there is a Category field (Chocolate, Comic Books, Music) to illustrate

                        the working for different cases (varying number of entities) as shown below. I have also added

                        more rows to show dates for a total of 5 quarters.

                         

                            The interesting part is that the Year & Entity have been blended into a single axis. Also there is

                        double layered coloring with a bit of bug fixing where in the graphs shows a blank (or all white) when

                        we select the same Start & End Date Parameters or The End Date being before the Start Date. Finally

                        Colorful images describing Entity Names are added.

                         

                        Also the Bars and the X axis automatically scale based on the values and number of entities

                        (shown in the 3 examples below).

                         

                             For the category Chocolate, the Period comparison was from 2016 Q4 - 2017 Q1

                        Screenshot 1.png

                         

                            Now lets look at another Category (Comic Books) for a different Period (2017 Q2 - 2017 Q4)

                        Screenshot 2.png

                         

                            Finally lets try the last Category (Music) for a different Period (2016 Q4 - 2017 Q4)

                        Screenshot 3.png

                         

                        The addition of the Date Parameters (Start & End) have simplified the calculations to a great extent.

                        I have attached the spreadsheet below, in addition to the twbx file. Here are the 3 Steps to creating

                        the above visual.

                         

                         

                        1) Datasource

                         

                        Here are screenshots of the 2 tabs in the Excel Spreadsheet. The first has the sales data

                        while the 2nd tab only contains the Category, Entity Name and the Entity (Number) that

                        we need and it can be automatically generated (Entity Name sorted in Ascending)

                         

                        Data 1 - Data.png

                        Data 2 - Entity.png

                         

                         

                        2) Data (Joining 2 Tabs)

                         

                           Data Join.png

                         

                           Do the Inner join as shown below (2 fields - Entity Name & Category)

                         

                           This step simplifies the problem by automatically assigning Entity Numbers for each Entity Name

                           based on Category. Now we are ready for analysis and creating Visuals.

                         

                         

                        3) Calculated Fields

                         

                        Here are the 14 Calculated fields & 2 Date Parameters (for start & end dates). The Parameters

                        were created using values from "Date" field (Parsed from "YearQuarter" text string)

                         

                        Calculated fields.png

                         

                        Formula 1 - Date.png

                              This had to be created as our original date field (YearQuarter) was a text field.

                         

                        Formula 2 - Max Entity Index.png

                              This automatically places the Bars evenly on the X axis and is independent of the

                              number of Entities / Entity Names

                         

                        Formula 3 - Difference (By Entity).png

                            This LOD calculation actually finds the difference between the 2 dates for each

                             Entity. In this example because all entities were different and used only once in

                             each category, the above formula works fine. In case you have one entity repeated

                            in another Category, then you need to use {FIXED  [Entity], [Category] : Sum..... }

                         

                        Formula 5 - Sales (Beginning of Period).png

                               This is the Sale value for the Start Date (Blue Bar)

                         

                        Formula 6 - Waterfall Calculation.png

                             The above Table calculation sets the position of each Entity Bar, starting from the Total  Sales for

                             Start Date and finally reaching the Running Total for the End Date. Essentially these are the  Colored

                            (Green/Red) Bars for the Waterfall Calculation.

                         

                        Formula 7 - X Axis A (Sales).png

                             This will set the X axis position for the Start & End Date Sales (2 Blue Bars)

                         

                        Formula 8 - Y Axis (Sales).png

                             This builds the 2 bars at the set X axis position for Start and End Dates

                         

                        Formula 9 - X Axis B (Waterfall).png

                              This sets the X axis position for  the colored bars (Green/Red) for the Waterfall Chart

                         

                        Formula 10 - Y Axis B (Waterfall).png

                             This places the Y axis position for the colored bars for Waterfall Chart

                         

                        Formula 11 - Color Vanish.png

                             This was for bug fixing, and shows a blank area (white) for the following 2 conditions:

                             A) If someone wrongly enters the Start date = End dates

                             B) If the End Date is before the Start date

                         

                        Formula 12 - Color 2 Difference.png

                             This symbolizes + (green) & - (Red)  depending on whether there is an increase or decrease

                             in the Sale value

                         

                        Formula 13 - Constant Y A.png

                            This was merely for creating the X axis Headers for the Start & End Years

                         

                        Formula 14 - Constant Y B.png

                             This was for creating the X axis headers for the Entities

                         

                         

                        Creating the Visuals & X Axis Headers (Dual X & Y Axis)

                         

                        Creating Chart.png

                         

                        Creating Headers.png

                         

                        To make it more appealing, you can duplicate this sheet, change the "Bar" to "Shapes" and add images

                        for each entity. This is the additional image layer shown in the dashboard.

                         

                        Hope this answers your problem, gives you a good idea on using alternate methods in solving problems

                        with a similar data structure and the steps to be taken along with the modifications, calculated fields.

                        Please don't hesitate to ask if you have any questions or need further clarity regarding the above steps.

                        Thanks for your time and patience and I am glad to have finally completed it. Best wishes !

                         

                        Sincerely,

                         

                        Soumitra

                        • 9. Re: Waterfall
                          Agnieszka Ludzik

                          Hi Soumitra,

                           

                          Thank you for your solution! It almost works.

                           

                          2 questions:

                          1) for some of the entities gantt bar is duplicated. It is shown as a part of the waterfall, and in the same column, it starts from the zero line again. I think it happens when previous or next entity is null or equal to 0. Would you have any idea how to fix it?

                           

                          2) If some entity is not assigned to category, it still appears on the axis, Can we show on the waterfall only entities assigned to the selected category? Fyi - I have entities repeated in some categories.

                          1 of 1 people found this helpful
                          • 10. Re: Waterfall
                            Agnieszka Ludzik

                            Thank you for your reply.

                             

                            The dummy data source and .twbx are attached.

                             

                            1) when you select Sport Category, 2017 value is missing, so duplicated gantt bar appears, as I mentioned above, it also happens when some previous or next entity is missing, but I cannot recreate it based on dummy data

                             

                            2) When you select Movie Category, Entity = 2 is missing, so we can hide it.

                             

                            Thanks!

                            1 of 1 people found this helpful
                            • 11. Re: Waterfall
                              Soumitra Godbole

                              Dziekuje ci !  Great news is that all bugs seem to be fixed after an hour of testing

                              with several data-sets. The funny thing was that the issue all along was data related !

                              Here is the screenshot of the modified enhanced solution.

                               

                              Also I have tested 2 different datasets (attached below) one with Year Quarter and the

                              other with only Year and they both work fine.

                               

                              Dashboard.png

                              In the 2nd solution, just to test the correctness, I removed UK from Movies Category and

                              I see only 2 entities instead of 4.

                               

                              Screenshot 2.png

                               

                              I had to modify your data to make it more challenging and be able to detect any possible bugs.

                              I also simplified a few of your formulas by removing unnecessary long formulas like Zn (not Zinc ).

                               

                              The reason for the 2 problems you mentioned earlier was simply because

                              the data was like this (Null Sales due to missing Entities in a given Period)

                              Old Look.png

                               

                              Instead if it had been like this, then we would not have had any problems.

                              New Data Look.png

                              Here you simply have 0 instead of the Nulls and so everything works fine.

                               

                              Data-source & Data Structure

                               

                              Your New Data (spreadsheet attached below) has 3 tabs which are as follows :

                               

                              1) Data

                              Tab 1.png

                               

                               

                              2) Entity

                              Tab 2.png

                              As you can see there are Entities missing in certain Periods. Also it does not matter

                              What Entity value a given Entity has. Eg:- FR =2 in Movie but FR=3 in Music Category.

                               

                               

                              3) Period

                              Tab 3.png

                               

                               

                              The main operation that made this wonderful data transformation is the following join

                              This process of restructuring and adding values (including 0) is called Data Scaffolding.

                              Data Join 1.png

                               

                              In addition to the above process a few formulas were modified (simple LOD adding Category) and

                              an important one that I mentioned yesterday (from previous years experience) is ISNULL (Sales,0).

                               

                              The only change in Period from Year Quarter to Years is using the DateParse formula (yyyyQQQ) to

                              (yyyy). If you like, I can add a formula that will look at the text and based on the length of characters,

                              it will automatically figure out if it is a Year or Year Quarter field and automatically do the DateParsing.

                               

                              Lastly, with your permission, I plan to clean up (remove unnecessary text) the earlier mess (all those

                              formulas) and put only the most recent ones that have been tested to work correctly (including the

                              latest solutions). This way it will look clean and informative (instead of messy and boring  ).

                               

                              This has been a long, fun, interesting but a great learning experience being different from other standard

                              questions. Quite a Waterfall and now for some music in 3/4 rhythm (pytala sie pani, Szla dzieweczka). Pa !

                               

                              Sincerely,

                               

                              Soumitra

                              • 12. Re: Waterfall
                                Soumitra Godbole

                                Hi Agnieszka !

                                 

                                I Hope your problem was eventually answered. Do let me know if you have any more

                                questions as I look forward to closing this thread soon else I might make history (or become

                                history) working on a problem that took the longest time to solve (almost 227 hrs).

                                 

                                I only pray that this does not drag on for 1001 hrs which is still better than 1001 nights

                                i.e. Sheherazade (Not the musical by Korsakov-Rimsky) but the medieval tale. Let me know !

                                 

                                Sincerely,

                                 

                                Soumitra

                                • 13. Re: Waterfall
                                  Agnieszka Ludzik

                                  Hi Soumitra,

                                   

                                  Your solution is great, but I couldn't get it work for my data. Finally, I have added rows for total for each category and period in my datasource and it works.

                                   

                                  Thank you for your time and all the support.

                                  1 of 1 people found this helpful
                                  • 14. Re: Waterfall
                                    Soumitra Godbole

                                    Dziekuje Agnieszka ! I am sorry to know that you had difficulty implementing it

                                    for your data, but glad to know that you were able to resolve the problem. Anyway

                                    it was great fun thinking and coming up with new ideas and creating cool visuals.

                                     

                                    By the way speaking of waterfall, for the first time I tried creating a short animated

                                    clip of a waterfall from 13 still images. It did not come out that good but was fun.

                                    I have attached the twbx file (version 2019.1). Hope it works alright.

                                     

                                    Dashboard.png

                                     

                                    Once again thanks for the wonderful Problem and Best wishes. Do Widzenya !


                                    Sincerely,

                                     

                                    Soumitra