1 2 Previous Next 23 Replies Latest reply on Dec 16, 2014 8:52 AM by Simon Runc

    SUMPRODUCT IN TABLEAU

    geeta.israni.0

      Hi All,

       

      A beginner in tableau, I came across this complicated step where I could not figure out a solution and require help. I would like to do a sumproduct of the column "Value" across different months. So sumproduct of Value with Mar-09 would be Value column and the Mar 09 value of all the 9 industries. I have attached the excel file as well incase a change in the data arrangement is needed.

       

       

      Any help would be appreciated.

        • 1. Re: SUMPRODUCT IN TABLEAU
          pooja.gandhi

          Geeta,

           

          Your question is not very clear here. Are you interested in seeing the total value per company per month/year? Or do you need all the industries on the view? In the excel how are you getting the 'value' across all industries? Capture.PNG

          Your requirement may not be this. But this is what I thought of, if I understand your question correctly. Arranging the fields as shown here will give you what you are looking for.

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

            Hi

             

            PFA the calculations that I am trying to get, in excel. May be this makes my issue clear.

            • 3. Re: SUMPRODUCT IN TABLEAU
              Simon Runc

              hi Geeta,

               

              Please find attached solution, although I have done a bit of reshaping of the data. I've generally found (and took a while for the penny to drop!) that any time spent reshaping the data is given back 10 fold in the long run!

               

              I've use the Tableau Excel Add in (Installing the Tableau Add-In for Reshaping Data in Excel | Tableau Software) to de-pivot your main data set (this will mean you don't have to create a calculation for each industry). I've then taken your C1.. table and created a a Look-Up tab (and added in the Industry next to the company column, so the 'join' can be joined on Industry).

               

              I've then used the data load interface to join the 2 (a left join on Industry field)

               

              Excel Joins.JPG

               

              Once we have the data in this format the SumProduct you want is a simple field of [Value]*[Multiplier]

               

              I've attached both the reshaped Excel, and TWB.

               

              Hop this makes sense, and helps. btw with the Excel add in this reshaping took me 2 mins.

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

                Hi Simon,

                 

                Thanks a ton for the solution!!!!! Worked perfectly. Was not aware of the add-in feature either. Thank you very much once again.

                 

                Just another simple question: is it possible to have a column added that would give the value of (Cell 4-cell 1)+1? The formula would stay consistent but it would start from somewhere in the middle of the row and go till the end. I say middle because as per the formula, the 1st 3 cells would anyway have  no value.

                • 5. Re: SUMPRODUCT IN TABLEAU
                  Simon Runc

                  hi Geeta,

                   

                  No problem, and yes the Excel Add-in is a very hand tool.

                   

                  I'm not sure exactly what you want on the second question, can you mock me up an example in Excel (the screen shot you sent before was helpful for me to see exactly what you were after), and I'll take a look.

                  • 6. Re: SUMPRODUCT IN TABLEAU
                    Simon Runc

                    hi Geeta,

                     

                    Yes I see what you mean, and yes you can, although you have entered the 'murky' world of table calculations. Table calculations allow you to control the direction, and level of calculations.

                     

                    In the attached, I've created the field you need, and a further field explaining how it works.

                     

                    The explaining field is called '3 Month Ago Value' and has the formula

                     

                    LOOKUP(sum([Value]),-3)

                     

                    This means bring me back the value 3 levels up in the 'partition'. When I bring this field in I can control what is counted as the partition (the default is Table Down, which in this case is what we want), we could equally have specified this in the compute using options (as shown below)

                    Controling Table Calcs.jpg

                     

                    The actual field you need I've called '3 Month Variance - Table Calc' and has the formula (ZN(SUM([Value]))/ LOOKUP(ZN(SUM([Value])), -3)) -1

                     

                    which means This Row Sales Value/Sales Value 3 Rows Up - 1

                     

                    There's lots of great explainations on how table calculations work. I'd start using the pre-built ones (add a field in, right click and goto 'Quick Table Calc' and selection one of the options, such as Percent Difference. If you then right click on the field again, and goto Edit Table Calc, and then click 'Customize' you can see how tableau is doing it), and then moving on from there.

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

                      Hi Simon

                       

                      Thanks much. got exactly what i was looking for. Now that since the lines are coming from different data sources, if I try to combine them in 1 graph, each of them takes the primary and the secondary axis. Intially, when i was plotting the same with pre-calculated values in excel, I had the secondary axis vacant wherein i was plotting the average value. Now, however, I am not being able to do the same. Attaching the images for your reference. For average, I was using the Window average formula in Tableau:

                      fomula_2.png

                      fomula_3.png

                      The dotted lines have been plotted in the secondary axis and are the average value of each of the lines.

                      • 8. Re: SUMPRODUCT IN TABLEAU
                        Simon Runc

                        hi Geeta,

                         

                        Glad it helped.

                         

                        If you mean you are blending the data, they yes you (probably) need to use Dual Axis (I say probably as if the 2 datasets are at the same level, you could create a calc field bringing in the value you want from the secondary source), but if you have joined them before coming into Tableau you can plot multiple fields on a single chart (show three calculated field on line graphe).

                         

                        Alternatively, you can use Reference Lines. There's no need to create a table calc as you can run the average over the whole view, and then right click on the Y-axis, and select add reference line. Within here you can change the reference line to the average of Sales (for example), and select 'Entire Table'. Also experiment with seeing what Pane and Cell do (just for your interest). If you want a different value for your reference line, drop it in the detail shelf, and you'll have access to it in the Reference Lines.

                         

                        ...the Rabbit Hole that is Tableau, is very very deep!!!

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

                          Indeed a Rabbit hole but pros like you have really get the better of it!!! Thanks for all the prompt replies. Guess I am done with my lot of questions for the day. Learnt a lot besides getting solutions to my queries. Would follow your posts as well. Ideal for aspiring analysts like myself.

                           

                          Best Regards

                          G

                          • 10. Re: SUMPRODUCT IN TABLEAU
                            Simon Runc

                            ...That's very kind, but if I know 20% of what Tableau can do I'd be surprised!! You should see some of the posts and work arounds from the likes of Joe Mako

                             

                            The forums are a great place to learn...I find that I often come to the forums with one question, and leave with something completely different (and usually better!! )


                            Have a good evening

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

                              Simon Runc

                              Hi Simon,

                               

                              Hope you are doing good. A colleague of mine posted a question which went unanswered. I don't know if that means we can not perform it in tableau. Just wanted to confirm with you if it's possible and if so, how should we go about it.

                               

                              Is it possible to carry out the following calculations entirely in Tableau? The data in "blue" is the data given.

                              The 'yellow' part is what we are expecting tableau to help us with.

                              The 'Weights' is a given value but I would like to be able to edit that within Tableau itself to see the impact it would have on the answers without having to refresh the data once imported within Tableau.

                               

                              Calc.png

                              Any help with the same would be appreciated.

                              • 12. Re: SUMPRODUCT IN TABLEAU
                                Simon Runc

                                hi Geeta,

                                 

                                Yes all good here thanks!

                                 

                                Yes you can do this in Tableau, but the SLOPE function isn't nativley supported so you need to create what the calculation actually does, and then, probably, use a parameter to multiply the value by the weighting (if it isn't in the data source). You might also need to do a bit of filtering, as I can see in your SLOPE formulas you don't necessarily always use all the values in each column. You may want to post me some data, so I can show you an example of the formula.

                                 

                                But basically the SLOPE function, in the background is doing the following

                                linearregressionequations.bmp

                                 

                                where a is the intercept, and b is the slope (this is just from the web http://www.statisticshowto.com/how-to-find-a-linear-regression-equation/ where there is this simpler form of the regression equations)

                                 

                                To create this slope equation in tableau we'd use the below window calculation, for column A (also Values and A could be swapped over depending on what is the independent and dependent variables (i.e. which is plotted against Y axis, and which X axis)

                                 

                                (size()*WINDOW_SUM(SUM([Values])*SUM([A]))-WINDOW_SUM(SUM([Values]))*

                                WINDOW_SUM(SUM([A])))

                                /

                                (SIZE()*WINDOW_SUM(SUM([Values])^2)-WINDOW_SUM(SUM([Values]))^2)

                                 

                                If you look closely you'll see I've just 'Tableaued' the above equation, using SIZE() for n, and Window Sum for ∑

                                 

                                I appreciate, It does look a bit scary...

                                 

                                There's a really interesting video in the tutorial section, explaining it a lot better than I have!! (from about 11 mins in, but the whole video is really useful)

                                Statistics Calculations | Tableau Software

                                 

                                Hope this helps!

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

                                  Simon,

                                   

                                  Thanks for sharing the video. It does cover a lot of statistical calculations we use on a daily basis.

                                   

                                  Here is an extract of the demo data. I have many more 'X's' but I guess an example from your good self will give me a head start and hopefully I would be able to take it on from there.

                                   

                                  Also, like I was mentioning, i think passing a parameter can help me make the weights dynamic but would i have to pass 'n' no. of parameters if there are n Xs with 1 weight for each? I am plotting my final values(slope value*weight)% as bars together on a chart.

                                   

                                   

                                  ** I just realized that I did not leave any formulae in the sheet for your reference so altered the sheet with the same. Also, I noticed a very interesting thing. Excel reads value only from the 1st non blank cell for 'Y' and ignores all the 'X' values that do not have a corresponding Y value.So excel will give me the same value if I start lower down the range from where both the columns have values.

                                  I guess that's precisely why the formula says 'known' X & Y values. Was reading up about the formula you referred to and came across the same.

                                  • 14. Re: SUMPRODUCT IN TABLEAU
                                    Simon Runc

                                    hi Geeta,

                                     

                                    Glad the formulas weren't too scary! If you're used to using statistics then the are fairly easy to follow.

                                     

                                    I've use the data in your screen shots as the attached Excel didn't seem to have Value and A, B...values. btw I've filtered out Nulls from the values (Row ID 1-4) as these were blank in your image.

                                     

                                    In this I've done it 2 ways, as I'm not sure how you will finally use the it. The 1st way, in the 'OneByOne' tab would require you to create a new slope calculation for each X/Y combination you need. And I've used a parmater to allow you to change the weighting. Both the calculated slope, and Answer A (weight x slope), are in the tool tip. I've potted the values, and used the Trend line so you can verify the slope is the same in both

                                     

                                    In the second, tab everything works exactly the same way, except you use a parameter to select either A or B. This way you only need one slope and answer formula, but obviously you can only see it for one Y at a time.

                                     

                                    Hope this makes sense, and is of help.

                                    1 of 1 people found this helpful
                                    1 2 Previous Next