12 Replies Latest reply on Sep 18, 2019 9:37 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.

                          • 10. Re: Waterfall
                            Soumitra Godbole

                            Hi Agnieszka,

                             

                                 You're welcome and I am so glad to hear that it works ! Regarding the 2 questions, I am fully aware

                            of the situations you mentioned as I had seen it years ago (history repeats itself ). I also have a

                            workaround but would need your help in providing me with some dummy data using Category and Field

                            (10-20 rows should be good enough).

                             

                               Also with regards to your specific questions, here are some fixes that immediately come to my mind :

                             

                            1) For a specific Category, lets imagine a situation in which an Entity is present in one period

                                 but is missing in another period. This poses problems and will create a similar scenario as you have

                                 mentioned. The reason being with a NULL Value, you cannot simply subtract it from a value

                                 and expect to get the same value. Instead use IFNULL(field,0) first before subtracting.

                             

                            2) In case you have one entity repeated in several Categories, no worries as each entity name will have

                                a different number (Entity index)  in each category depending on whether there are other entities.

                             

                            Lastly providing me with some dummy data (fake), will definitely help me to implement the above

                            techniques and come up with a proper solution. Awaiting your reply with some sample data. Bye Now !

                             

                            Sincerely,

                             

                            Soumitra

                            • 11. 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!

                              • 12. 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