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

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

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.

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

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)

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

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

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

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.

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

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!

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

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

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.

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

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

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

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.