11 Replies Latest reply on Jan 10, 2014 9:48 AM by Matt Lutton

    It should be simple...but...How can I aggregate the results by sales frequency

    Juan Pablo Bruzzo

      I would like to analyze the data making a segmentation based on the purchase frequency of my customers. Imagine that I have a simple data base like the following.

      Month Customer  Sales

        1          Id1          $10

        1          Id2          $20

        2          Id3          $30

        2          Id2          $50

        2          Id2          $40

        2          Id1          $80

       

       

      I would like to agregate the results by numbers of sales...in this example would be:

      Month 1

      1 accumulated Sale: 2 user / $30 (Id1 and Id2)

       

      Month 2

      1 acc, Sale: 1 user / $30 (id 3)

      2 acc. Sales: 1 user / $80 (id 1)

      3 acc...Sales: 1 user / $90 (id 2)

       

      It seems very simple, but I can find the way to solve it.

        • 1. Re: It should be simple...but...How can I aggregate the results by sales frequency
          Matt Lutton

          It is pretty simple in Tableau.  The question is: how do you want to display the resulting data? Using your sample data, I was able to create the following in a matter of seconds.  This is what makes Tableau so much fun to use.

          Untitled.png

           

          This simply uses COUNT(Customer) for each month.  We'd use COUNTD if we only wanted to count unique IDs in each month.

           

          Some suggested reading: Blue things and Green things - The Information Lab  Understanding the difference between discrete/continuous and dimensions/measures--and the impact they have on a view in Tableau--is pretty critical (I still can struggle with this in advanced cases).   But on a very basic level, everything in your view is dependent on the dimensions you place on the shelves.  In this case, by starting with Month # on the rows shelf as a Discrete (blue pill) Dimension, we ensure that is the level of detail for this view.

          8.1 workbook attached, if you want to examine what we've done.  If you haven't used Tableau a lot, I'd suggest the Training Videos, as well as the many blogs that are available--read up, study, practice, read some more, practice some more.  You'll get the hang of it, but it doesn't come overnight.

           

          Cheers

          1 of 1 people found this helpful
          • 2. Re: Re: It should be simple...but...How can I aggregate the results by sales frequency
            Juan Pablo Bruzzo

            Matthew, thanks for your helpful reply. It is clear the tableau is a fantastic tool, but I'm still at the beginning of the learning curve. I really enjoyed your suggested article.

             

            Regarding my question, the output that I need it is a little different from the one you sent.You can see bellow, the  info that I'm trying to get. Do you know how to tackle this?

             

            MonthAccumulated Customer Freq.# CustomersMonthly Sales
            112$30
            211$30
            221$80
            231$90 ($40 + $50)

             

            Thanks in advance.

            • 3. Re: Re: It should be simple...but...How can I aggregate the results by sales frequency
              Matt Lutton

              You'll need to describe the logic behind these columns.  The part I really don't understand is the Accumulated Customer Frequency, particularly for Month 1, where you have 2 customers and 2 separate sales.  Why is the value 1 for month 1?

               

              This will definitely require Table Calculations, because you are wanting to show this data at two levels--Month and Customer

               

              I also don't understand why you've aggregated month 1, but not month 2 in your mockup.  Really having trouble understanding this mock up.  Based on your mockup, you want two different things for Month 1 and Month 2--so, as far as I can tell, you'd have to use two separate sheets on a dashboard.  Unless your mock-up is just incorrect... is it?

              • 4. Re: It should be simple...but...How can I aggregate the results by sales frequency
                Juan Pablo Bruzzo

                Thanks for your email. What I want to show is monthly sales segmented by

                customers sales frequency. We have different type of customers... from

                sporadic customers (1 purchase per year), to very heavy users (+10

                purchases per year). In this analysis we want to understand the different

                kind of purchases depending on how many historical purchases they have at a

                specific moment.

                 

                So in this simple example in month 1 we have two customers, with one

                purchase per customer. As this is the first month, we only have customers

                that made 1 purchase in their "life"....so this is the only segment

                 

                In month 2 Id2 made two different purchases so for this month his is going

                to be in the segment on 3 cumulative purchases. Id3 made his first

                pruchase, that is why he is going to be under the segment of "1 historical

                purchase".

                 

                Hope you understand what I'm trying to figure out. Once again thanks for

                you help.

                 

                J

                 

                 

                On Thu, Jan 9, 2014 at 4:14 PM, Matthew Lutton <

                • 5. Re: It should be simple...but...How can I aggregate the results by sales frequency
                  Matt Lutton

                  Well, although I don't understand the view you are trying to build, I created it nonetheless.  There are quite a few tricks in this workbook, Several Table Calcs, optimizations (IF FIRST()==0 then...), two sheets on a dashboard, removed borders from the sheets so they'd appear like one table, Unchecking show headers on the Month 2 sheets, so Month # is repeated for each row, etc.  Feel free to ask questions.  Note I didn't spend much time trying to line these columns up, but it can certainly be done.  I'm not sure how you'd address this in one sheet.

                  Untitled.png

                  8.1 workbook attached.  I'm not even sure my logic for Accum Frequency is accurate.  With Table calcs, you have to set them up to compute across the dimensions in your view.  For all the Table Calcs, that was set up as Edit Table Calculation>>Advanced>>Month, then Custom in the Addressing column.

                   

                  Hope this helps.  Many of these techniques are discussed in great detail on this forum if you do some studying on Table Calculations.  I'm still not an expert on them, by any means--but I learn more by participating in forum threads like this one, so thanks!

                  • 6. Re: Re: It should be simple...but...How can I aggregate the results by sales frequency
                    Juan Pablo Bruzzo

                    Once again thanks for your post. It was very helpful your example not only to solve my specific problem, but also for a general understanding of to the tool and to learn some specific tips.

                     

                    I'm still having some problems to get to the output that I'm looking for. You can see bellow the partial result that I'm getting (using your workbook as a base). I cannot calculate the number of customers correctly...As you can see bellow for all the cases the results are "1". From other side the blue squares represents accurately the number of  customers (per acum frequ)...

                     

                    Probably it is very simple, because it seems to be only a visualization issue, but I cannot get it correct.

                     

                    Once again thanks for your help!

                     

                     

                    Result_Tableau.png

                    • 7. Re: Re: It should be simple...but...How can I aggregate the results by sales frequency
                      Matt Lutton

                      In my workbook, the count was a table calculation; something like:

                       

                      IF FIRST()==0 THEN WINDOW_COUNT(COUNTD(Customer)) END

                       

                      The IF FIRST()==0 is used to only return the value once, in the top row, rather than in each row.  Table calculations must be set up properly to compute correctly--if you can post your workbook, I'll be happy to help you out with this. 

                      • 8. Re: Re: Re: It should be simple...but...How can I aggregate the results by sales frequency
                        Juan Pablo Bruzzo

                        Bellow is the final table that I'm trying to build. I'm also attaching the workbook so you can check what I`m doing wrong.

                         

                         

                        Tabla Final.png

                        • 9. Re: Re: Re: It should be simple...but...How can I aggregate the results by sales frequency
                          Matt Lutton

                          I'm sorry, but you keep revising the data and each time it seems a little different.  I don't understand at all.

                           

                          I'm gonna let someone else try to help you.  I just don't have a clear description or understanding of what you want, and it doesn't make any sense to me.

                          • 10. Re: It should be simple...but...How can I aggregate the results by sales frequency
                            Juan Pablo Bruzzo

                            The question was always the same, but probably not correctly drafted from the beginning. I`m sorry about that. Thank you anyway it was very helpful your different posts.

                             

                            Hope someone else of the community can help.

                             

                            Best

                            • 11. Re: It should be simple...but...How can I aggregate the results by sales frequency
                              Matt Lutton


                              No reason to be sorry--it takes time to know what to expect on these forums and in Tableau!

                               

                              I would suggest you open a new thread (since you've marked this one as answered correctly), with this new sample data, and a clearly worded description of what you want (list out the calculations you want, and how they should be calculated) as well as your mockup of the intended results.  Then, other forum members will be more likely to help you.  Because this thread is "answered", nobody knows you are still searching for clarity here.

                               

                              Cheers and good luck.