10 Replies Latest reply on Aug 5, 2015 10:10 AM by rakhesh.nellikoppa

    SOW Calculation (Share of wallet )

    rakhesh.nellikoppa

      Hi ....

       

      I am looking for formula or way to calculate SOW (share of wallet )

      Example : it’s the number of times a given customer fills up their car’s gas tank one month at their own pumps divided by the total number of times the same customer fills up their car’s gas tank that entire month

       

      I have all the retailer data and need to find out SOW for any selected retailer ....

       

      SOW = Number of times customers have shopped in any retailer / Number of times customers have shopped in all the retailer

       

      When I select any retailer in filter then my divider will also restrict to that particular retailer but that should not be the case and I cant hard code that ...


      So need your help or any way I can calculate the same


      Thanks in advance ...


      Regards,

      Rakhesh

        • 1. Re: SOW Calculation (Share of wallet )
          Simon Runc

          hi Rakhesh,

           

          There are a couple of ways we can do this, but to find which is best can you let me know if you are on Tableau 8 or 9?

           

          The problem you have (which looks like you've worked out) is that a filter, filters out the data for that retailer so using that method you'll always get 100%. In Tableau 9 we can get around this with LoD calcs, but there is a trick we can use in 8 too.

           

          If you can also post a sample of your data (here's a quick video on how to create anonymized version)

           

          Video demonstrates how to anonymize your workbook/data

           

          That way I can give you an exact solution

          • 2. Re: SOW Calculation (Share of wallet )
            rakhesh.nellikoppa

            Thanks for the reply Simon Runc....

             

            I am currently working on Tableau 9 ...

             

            Yes it will come always 100% for any particular retailer if I select from filter ....

            I will take sample data set and provide the twbx, mean time time if you know how we can calculate the total excluding the filter selection would me very helpful ...

             

            Thanks again ....

             

            --Rakhesh

            • 3. Re: SOW Calculation (Share of wallet )
              Simon Runc

              ...Excellent 9.0 makes life much easier! Once you post the work book I can give you exact solution, but the general idea is to use LoD Calcs to populate every row in your data with the required aggregations, so when you filter the Total and Selected retailer counts are still there.

               

              so you'd create one LoD calculation for the Total number of retailers visited. Something like (I've assumed COUNTD here but will work with any aggregation)

               

              Total Visited Retailers, lets call it 'Total Retailers Visited LoD'

              {FIXED [Customer ID]: COUNTD([Retailer Name])}

               

              This will put the total numbers of Distinct Retailers a Customer has visited (if you want by Month, you need to create a Month version of your date using DATETRUNC, and then also bring this into the LoD Calc, like {FIXED [Customer ID], [Month]: COUNTD([Retailer Name])})

               

              Using this field you can then create your calculation as

               

              COUNTD([Retailer Name]/AVG([Total Retailers Visited LoD])

               

              btw as the LoD is applied to every row we need to use AVG (or MIN/MAX/ATTR) so that we don't SUM up these values.

               

              This is the basic method, but depending on your data structure, Level of Detail in the Viz, and other Dimension you might be slicing by, we may need to adapt a the formulas slightly.

              • 4. Re: SOW Calculation (Share of wallet )
                rakhesh.nellikoppa

                Hi Simon,

                 

                I will go in detail about your reply and try to understand how it works. I have attached the twbx file for your ref and have added dummy data.

                 

                Thanks again for the quick response .... really appreciated.

                 

                Regards,

                Rakhesh

                • 5. Re: SOW Calculation (Share of wallet )
                  rakhesh.nellikoppa

                  yes I need it in month level but not sure you have all the data in attached file, please feel free if you need anymore fields required to calculate SOW for given month ....

                   

                  Thank you.

                   

                  Regards,

                  Rakhesh

                  • 6. Re: SOW Calculation (Share of wallet )
                    Simon Runc

                    ...thanks for posting a workbook. I'm away from my laptop for the next few hours, but will send you a solution when I get back...unless someone else can solve it for you in the meantime (now you have posted a workbook it should be more attractive to solve!!)

                     

                    Sent from my iPhone

                    • 7. Re: SOW Calculation (Share of wallet )
                      pooja.gandhi

                      Hi Rakhesh,

                       

                      By looking at your data, it looks like you are going to get a SOW value of a 100% for all retailers. Because in your dataset, each user has purchased only at one retailer for the entire month. I added just a few more rows of dummy data to show you what I mean. See user ID 708, I added dummy data for retailer F and created 3 calculated fields.

                       

                      Calculation 1: { fixed [User id], [Retailer], [Purchase Date] : countd([Receipt ID]) } - this is going to give you the count of times a user purchased at a specific retailer in a specific month. So user 708 purchased 2 times in October 2014 at retailer F and 16 times at retailer M.

                       

                      Calculation 2: {fixed [User id] : count([Receipt ID]) } - this is just counting the number of times user 708 purchased at ALL retailers.

                       

                      Calculation 3: ([Calculation1]/[Calculation2]) - This is your SOW. So 11.11% of user 708's money went to retailer F and 88.89% went to retailer M.

                       

                      Capture.PNG

                       

                      If you look at sheet 4 on the attached, retailer M also got 100% of money from users 30878 and 42551.

                       

                      Tip: Always lay out all the fields in your workbook in a flat data format to see how the data is structured and you will always have an idea how the calcs will work.

                       

                      I am looking forward to see what Simon comes up with, he might have a better way to do this.

                       

                      Hope this helps.

                      • 8. Re: SOW Calculation (Share of wallet )
                        rakhesh.nellikoppa

                        Thanks Pooja, exactly what I wanted to calculate .....

                         

                        Thanks for the detailed explanation and correcting the data too ....really appreciated it...

                         

                        I will apply the same in my real data and let you know if anything ....

                         

                        Thanks again and thanks lot Simon, your input was very helpful to understand about LoD. I will go through more about it to update myslef.

                         

                        --Rakhesh

                        • 9. Re: SOW Calculation (Share of wallet )
                          Simon Runc

                          hi Pooja,

                           

                          Many thanks (I have a 6th sense when a deadline is looming...and seemed Rakhesh needed an answer ASAP!)

                           

                          No that's exactly the way I'd have done it (we could start messing about with TOTAL, and Table Calc filters, but why make life harder!! - and these LoDs will work whatever the pill arrangement)

                           

                          Rakhesh, One very good thing here is that as Pooja has included 'Purchase Date' in the FIXED calculation, Tableau will use this dimension at whatever level you display (so if it you show by Month, it'll be by Month, if by Week then the calculations will be worked up by week, so no need to get involved with those DATETRUNC functions, unless you want to fix the calculations at a specific time period).

                           

                          I'd also point you in the direction of these 2 great resources on LoD calculations, which give (the White Paper) a good understanding of what they do and how they work, and (15 Uses) some great use-cases

                           

                          Understanding Level of Detail (LOD) Expressions | Tableau Software

                           

                          Top 15 LOD Expressions | Tableau Software

                          1 of 1 people found this helpful
                          • 10. Re: SOW Calculation (Share of wallet )
                            rakhesh.nellikoppa

                            Thanks you Simon, I was going through "Top 15 LOD expression", its great explanation and it will really make life easier.

                             

                            Good to learn and thanks for sharing ....