1 2 Previous Next 23 Replies Latest reply on Dec 16, 2014 8:52 AM by Simon Runc Go to original post
      • 15. Re: SUMPRODUCT IN TABLEAU
        geeta.israni.0

        Hey Simon!

         

        Thanks alot! Was indeed very helpful!

        • 16. Re: SUMPRODUCT IN TABLEAU
          geeta.israni.0

          Hey Simon,

           

          A doubt regarding the initial discussion we had wherein you taught me the Excel Add-In feature. I was trying to go a step further with it. I have added Categories and would like to have the Weighted Avgs. calculated category-wise. Creating table calculations within tableau (using a calculated field as an input value) prompts that an already aggregated field can not have aggregation put to it again.

           

          I would plot it on the same axis and have ref lines for each category weighted avg curve that i plot. I added another excel file and a tableau workbook in my original post that has highlighted the values I am trying to get tableau to calculate.

           

          I know I have been bothering you with a lot of stuffs but would you please have a look at it?

          • 17. Re: SUMPRODUCT IN TABLEAU
            Simon Runc

            hi Geeta,

             

            Not a problem, and I love a good challenge! and this one is a little more complicated that you might initially think! but should give you a bit of understanding on how Tableau thinks!

             

            Firstly the data you posted wasn't quite the same as in the Tableau Model, so I've re-created the 'Tableau_result' tab in the attached so you can verify that both calculations equate to the same.

             

            In the attached workbook I've done 2 tabs, one (with append 'working') to try and explain what it happening 'under the hood' and the second one the final solution.

             

            As you are trying to create a calculation dependent on the level of granularity in the data, but aggregated over a higher level you'll need Table Calculations.

             

            The first table calculation I've created (which is just for explanation) is called 'Multiplier Table Calc' with the equation WINDOW_SUM(MIN([Multiplier])). I've also put in the original field to show you what it's doing.

            I have to keep Industry in the detail shelf, as I need Aggregated calculation to be able to reference this, and this is why every figure appears multiple times (9 in fact, one for each industry). This is the same a using detail in a scatter plot, where you want Tableau to plot each 'detail'. When you see a table this is just a text plot (but we can get around this later). If you look at Multiplier (which I've asked to be an ATTRIBUTE) you can see Tableau plots the value for each Industry. As I want a sum of this, I can use the formula given above (using MIN, as table calculations can only run over aggregated fields. I could have used Max, Average...). By specifying the level I want this run over (Industry) you see it brings back the SUM (and doesn't add up every occurrence in every row). As we have industry in the detail shelf we get this result 9 times.

             

            Similarly I've created a Table Calculation for the final result, and again run it by Industry. This one is called 'Weighted Values - New' and has the formula

             

            WINDOW_SUM(SUM([Multiplier]*[Value]))/

            [Multiplier Table Calc]

             

            I hope by leaving the 'repeated' values in, you can see how it's working.

             

            Now the final bit is to get it plot the result just once. We add an extra bit to the formula IF FIRST==0 THEN...END so the final formula (called 'Weighted Values - New Display') is

             

            IF FIRST()==0 THEN WINDOW_SUM(SUM([Multiplier]*[Value]))/

            [Multiplier Table Calc]

            END

             

            What this does is only plot the first value. In actaul fact rather than -7.09% -7.09%....for the 1st result, it actaully equates to -7.09% NULL NULL... but as Tableau doesn't plot NULLs we just get the single result in view.

             

            Again hope this makes sense, and is of use.

            • 18. Re: SUMPRODUCT IN TABLEAU
              geeta.israni.0

              Hi Simon,

               

              This was quite complicated but with your clarified workings and detailed explanation I am figuring this out step by step. The final table does match with the excel values perfectly. But I see the line graph option disabled when I try to pot these values.

               

              This was when I was trying to figure the solution out (of course its redundant now that I see how you finally got to it but this was what I wanted to display finally):

               

              graph with ref lines.png

              The red line is from another data source and plotted in the secondary axis with ref line selected to show the averge.

              The blue and green lines are the weighted values of Category A and B (which you have already produced in your calcualtions). In place of the blue dotted ref line I was trying to get ref lines for both the Blue and Green lines showing their averages respectively but there was no separate options.

              • 19. Re: SUMPRODUCT IN TABLEAU
                Simon Runc

                hi Geeta,

                 

                I think you are referring to the 'Show Me' being greyed out for line charts. The 'Show Me' selector, just sets up the various Row/Column Shelves, and Detail/Colour/Size...pane for each of the various chart types, a bit like a short cut.

                 

                ...however you can create these yourselves, and 'force' Tableau to plot things in the way you want. I tend to see Tableau as a drawing tool. For example if you create a TreePlot (sometimes called a Tile Chart) using the show me, you see that it has set the size on something (say Sales), the colour on something (say profit), and the mark types as Squares. If you now change the mark type to Circles, you will now have a (not that I condone such a marketing type chart!!) a Bubble Chart. Try using a few different Chart Types from 'Show me', and see what settings have been put into Column/Row Shelve, and Marks Card, and hopefully you'll see how you can control the visualizations.

                 

                In the same way in the attached I've asked the Marks to be lines, I've put Date on Columns (and asked it to be continuous, although this is optional), The Weighted Values on Rows, and Colour by Category, and I get the chart I want. btw I've had to leave Industry in the detail marks as the Window Calcs need this level to run over.

                1 of 1 people found this helpful
                • 20. Re: SUMPRODUCT IN TABLEAU
                  geeta.israni.0

                  Simon,

                   

                  Thanks alot for the detailed explanation and much needed assistance. Works perfectly on my data.

                   

                  Thanks once again!

                  • 21. Re: SUMPRODUCT IN TABLEAU
                    Simon Runc

                    Excellent! It took me a while to adapt from other BI systems, where you choose the 'Chart Type' to Tableau where you, kind of, create the Viz from the components of Marks, Size, Colour...etc. Once I'd got that you can start creating some really powerful Vizes (Even create your own)...for example on a line chart, try dragging a measure on the size tile, and the thickness of the line reflects the measure...i found that I can get an extra dimension on view which is still intuitive to view. Or for some real 'free form'/'info-graphics' check this one out! http://public.tableausoftware.com/profile/jeffs8297#!/vizhome/MusicMajor-DataMiner/MusicMajorDataMiner

                    ...just to show the 'art of the possible'!

                    • 22. Re: SUMPRODUCT IN TABLEAU
                      geeta.israni.0

                      It's amazing! I think its gonna keep me busy for a couple of days when I get time after work and deliverables.:)

                       

                      I have been following your good self along with other Tableau genius in the likes of Joe Mako and Jonathan Drummey and I am truly amazed at the depth of tableau capabilities. Many call it a 'presentation layer' but Tableau can be so much more if we can just explore the possibilities. The videos/URLs you have been kind to share with me or even your detailed explanations and demo workbooks; when I go through and understand them I feel my brains expanding. Gives me more enthusiasm to explore Tableau further! Please let me know if you have blogs etc wherein I can follow and learn from you and get access to more such ideas.

                       

                      Thanks again.

                      Cheers!

                      • 23. Re: SUMPRODUCT IN TABLEAU
                        Simon Runc

                        Yes that one is a very cool viz...I can imagine all CVs being that way in the future!!

                         

                        There are lots of great blogs out there if you check out the following (apologies for the Atheon plug!)

                         

                        http://www.atheonanalytics.com/tableau-resources/

                         

                        Drawing by Numbers is really good, and the Tableau Data Blog Finder | Tableau Public is a Viz of Viz-Blogs!!

                        1 2 Previous Next