4 Replies Latest reply on Apr 16, 2013 3:55 AM by David Baudrez

# Sales \$ Bands/Bin calculations - issues at aggregated level

Dear Tableau friends;

Find a few questions my team and I have been struggling with for 2 weeks. Maybe some of you faced similar issues and could give us some hints.

When trivial in Excell/Access, we are banging our heads to solve it within Tableau... OK we are not yet experts, reason why we are looking for your support. Appreciated.

Based on Super Store Sales example; we are struggling to perform a couple of queries

1. Identify and Classify Products transactions by bin-size - what we call "sales \$ bands".
• Classify products with a sales of 0-2500\$; 2501-5000\$, 5001-10000\$,...
• In a particular Year, Quarter or Month
• For a particular time period (over multiple month / quarter / years
• => seems ok with the creation of a calculated field: (bookings band - simple) see tab 1&2 in example attached
2. Use this "Sales \$ bands" concept at aggregated level => by customer; by category or sub category, by region
• => Seems working by the creation of a calculated field (booking bands - sum) see tab 3 of example
3. Counting and Classifying # of customers by Sales \$ Band: that is where the struggle begin
• Instead of seeing 2 transactions in a particular Sales\$ Band we  would see the total for that customer and then apply the bin formula.
• Then we want to count the number of customers in each sales\$band
• =>Seems the (Booking bands - sum) cannot be used as a Dimension but only as a measure (as aggregated)
• =>Seems the Count Distinct function is not showing. that could have help in 4th tab to differentiate by transaction or customer level
• =>We have been trying to link the Sales measure with the customer dimension but without success
4. Finally, we would like to apply similar concept but looking at Frequency Bands: classifying customers by # of transactions they have done in a particular period of time : 0-2; 3-5; 5-10; >10 and use that as another dimension to analyse our customer base.

We received the hint that the solutions should exist by applying advanced table calculations to the dataset. But we are not skilled enough to do that by ourselves. We are about to hire an expert but it gonna take another few weeks. In the meanwhile; we would be very keen to get some feedback from the community.

Many thanks

David Baudrez

dbaudrez@cisco.com

• ###### 1. Re: Sales \$ Bands/Bin calculations - issues at aggregated level

David,

You're right that some table calculations are required. you also need to make the COUNTD (count distinct) function available by switching to an extract -- this function is not supported by MS Excel. You also need to un-tick the 'Ignore in table calculations' option on the Booking bands field.

Please take a look at tab 4 of the workbook I have uploaded, does this achieve what you need?

Best of luck,

Robin.

1 of 1 people found this helpful
• ###### 2. Re: Sales \$ Bands/Bin calculations - issues at aggregated level

Hi Robin,

Thanks for your feedback and indeed; your example is helpful to progress;

Having said that doesnt solve our issue as there are some calculation mistakes with this formula

From the sheet you sent back: if you take the 25-50K line in 2011 => value is 10

Looking at the underlying data we see:

- only 1 customer Cindy Schnelling with 10 in distinct customer count

Deep diving one level down:

only 7 lines; only from 1 customer; for a total of 27K

What I was expecting to see is the count distinct being 1 instead of 10: Only 1 customer in that band in 2011. With 10 transaction for a sum of 27K.

Any suggestion?

I got a couple of advanced users that tells me to do the consolidation by bin and flagging outside of Tableau in order to workaround. But this would be definitly a sub-optimal solution for us as we are really looking at building those booking bands in a dynamic way...

For example; I would be able to do the math and show the demography by booking bands for the entire business and for all the regions. Then play with regional or category filters to recalculate my demography at the fingertip. I wish you are following me :-).

Thanks for your other feedback/hints

.

• ###### 3. Re: Sales \$ Bands/Bin calculations - issues at aggregated level

HI David,

The 10 figure for 2011 sales 25k-50k is correct -- check it out on a separate sheet by putting Customer on Rows, Sum(sales) on text and Year(order date) = 2011 on filters

The reason you were getting only 1 customer up when looking at the data is because of the way the data is being displayed as part of the table calculations -- each customer is being calculated individually and totaled individually -- i.e. there are 11 11's, and picking the top one, just gives one instance of the 11!

• ###### 4. Re: Sales \$ Bands/Bin calculations - issues at aggregated level

Thanks Robin, it seems very close to what we were looking for.

I am deepdiving a bit further and may come back to you with a few more sub-questions.

Big thanks again!

Attached the VIZ created based on my original file and request.