9 Replies Latest reply on Jun 8, 2018 7:17 AM by Alix ROTH

    Sales Bucket by customer

    Alix ROTH

      Hi all,

       

      I'm new to Tableau and facing my first issue. I hope someone can help me

       

       

      I'm creating a report and I would like to be able to filter my sales by customer bucket on all the views

      This would help drive conclusion such as:
      -> In every Regions, the sales from small customers (= customer from the lowest bucket) are increasing compared to prior year.


      It means that  I would need to have a flexible aggregation of the sales to the customer level, that change depending on the filters I apply or the view I display.

       

      What I have done currently is :

       

      1. Measure "SALES FIXED CUST" :


      { FIXED [Customer]:SUM([Sales])}

       

      2. Dimension:

                         

      IF [SALES FIXED CUST]< 50 THEN "Less Than 50"

      ELSEIF [SALES FIXED CUST] <= 100 THEN "Bt 50 - 100"

      ELSEIF [SALES FIXED CUST] <= 500 THEN "Bt 100 - 500"

      ELSEIF [SALES FIXED CUST]<=1000 THEN "Bt 500 - 1000"

      ELSEIF [SALES FIXED CUST] > 1000 THEN "Greater than 1000"

      END


      It works if I display the measure without any filter, but I want to choose the sales of period, it's wrong. If I add the period to the {FIXED} formula, on a full year it's wrong. Same by Region, Product etc...

       

      I'm attaching a workbook with what I've done so far.


      Could someone help me with that?

       

      Thanks,

       

      Alix

        • 1. Re: Sales Bucket by customer
          Simon Runc

          hi Alix,

           

          Welcome to Tableau and the Community!

           

          So firstly you have attached a .tbw and not a .twbx (so I can't access the data). If you package it up, and re-attach we can take a look.

           

          However I can have a good guess at the issue. So FIXED LoDs (as you are using here) are computed before any "regular" filters are applied, which is why the Segments/Buckets aren't updated when you change filters. You can however "bump" a filter up the calculation pipeline, but making it a "context" filter. If you right click on a filter (in the filter shelf) you'll see an option "add to context". Select that (the filter will also turn gray/brown to show it's not in-context and it will now be applied before the LoDs are computed.

           

          Hope that helps solve the problem, but if not attach a .twbx and I'll take a look

          1 of 1 people found this helpful
          • 2. Re: Sales Bucket by customer
            Brian Cronin

            Try changing the filters to be "Context Filters" You can do this by right clicking the field in the filters shelf and selecting "Add to Context"

             

            This will have the fixed calculation calculate AFTER those filters are taken into account.

             

            Careful on this though as on larger datasets Context filters can begin to cause performance issues.

             

            Hope this helps

            2 of 2 people found this helpful
            • 3. Re: Sales Bucket by customer
              Jim Dehner

              Good morning

              Welcome to Tableau and the forum

              You posted a twb workbook that does not contain the data - you need to post a twbx workbook that includes data

               

              If I  understand the issue you have created a fixed calculation that gives you the total sales by customer and you want to use it but filter or otherwise segment the view by region and date(year)

              Try adding those dimensions to you LOD {fixed [region],[customer],year(date): sum(sales)} 

              Tableau will create permutations of all the year,region,customer combinations that can be used in further calculations

              Jim

              If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

              • 4. Re: Sales Bucket by customer
                Alix ROTH

                Hi Simon,

                 

                Thank you for your answer,

                 

                Just tried and it looks more correct, but there is still some differencies with the number I should find.

                 

                FYI- the workbook I've attached is not really the one I'm working on but it reflects the type of data I have -- in this workbook put the filter to context didn't change the values at all.


                More, I'm working with a large database, won't it hurt the efficiency?

                 

                 

                Thank you
                Alix

                • 5. Re: Sales Bucket by customer
                  Simon Runc

                  That is strange, are you able to package up your example workbook? and let me know what you are expecting for a value (once you apply some filtering) and where Tableau isn't returning the correct value. will just help me isolate where the problem lies.

                   

                  Yes fixed LoD's will have an impact on performance. How large is your data? (large is relative!) as if it's hurting performance, there are some other options (either using INCLUDE/EXCLUDE LoDs, or Table Calculations). LoDs are the easiest, but there might be other options.

                  • 6. Re: Sales Bucket by customer
                    Alix ROTH

                    Hi Jim,

                     

                    The thing is: I want to be able to aggregate the sales by customer on a full year basis if I choose : full year, and on a period basis if I choose a specific period. Thus, I can not fixed the date in my formula..
                    Same for Region, I have two levels of regions (Region and Country). If I select country: I want it to aggregate the sales by customer at the country level. But if I select region: I want to aggregate the sales by customer at the Region level.

                     

                    Not sure if my explanation is clear...

                    • 7. Re: Sales Bucket by customer
                      Alix ROTH

                      It Works now !!
                      Let me know if the file attached works as I have a last question:
                      Why does my dimension : customer bucket, created based on the measure "Sales FIXED CUST", impact the measure "sales" ?

                       

                      Currently my excel file has 800 000 rows for 30 columns.. is that large?

                      • 8. Re: Sales Bucket by customer
                        Simon Runc

                        hi Alix,

                         

                        Great news!

                         

                        So 800k isn't particularly large for .hyper extracts (I tend to have to think hard about my calculations and efficiency at around 50M+...although this is a rough rule of thumb, and data with low compression and/or hundreds of columns will start performing slowly with more than a few million). If the performance is fine for you, then I'd say stick with the LoDs.

                         

                        So the reason is that the FIXED LoD is applied to a customer and as customers can shop in multiple regions the sales against them will be their total sales, as per this example here

                         

                         

                        It's cleverer and better than this (and not actually how it works!), but one way to look at a FIXED LoD is as a SUMIFS (in Excel...which I just about remember from a past life!)

                         

                        The LoD you have is like doing a

                         

                        SUMIFS('Sales Column', 'Customer Column', 'Bert Nicholson')

                         

                        So if I add region to the LoD...then they would be the same (with the context filter on period), or if you had a context filter on region and set that to Estonia, the LoD Sales would be 23.

                         

                        Hope that makes sense?

                        1 of 1 people found this helpful
                        • 9. Re: Sales Bucket by customer
                          Alix ROTH

                          it does !! thank you for all your answers !!