4 Replies Latest reply on Dec 23, 2018 11:13 PM by Laxmikant sahu

# Bin Analysis

Hello,

I have scenario which need to be addressed.

I have items, sub items and their respective combination sales count for each hour in a day.

I need to aggregate the items and sub items count into various bins.

Sample Data

Hour

Item        Sub Item         1           2         3          4

Item 1     Sub Item 1      6           15

Sub Item 2      35          4

Item 2     Sub Item 1      100       50

Sub Item 2      15         45

Requirement:

Hour

Bins                                 1          2          3

0-20                                 2          2

20-40                               1          0

40-100                             1          2

For ex: 0-20 Bin - if the count of sum of item and sub items combination which have been sold in first hour then group in 0-20 Bin

Likewise i have group in different bins

Thanks

Saravan

• ###### 1. Re: Bin Analysis

Simon Runc has answered some tough bin questions in the past. Any help?

-Diego

• ###### 2. Re: Bin Analysis

Thanks for the ping Diego...

Hi Saravanan,

So I've done this a couple of ways (one using Tableau default bins, and the other using "Human Readable" bins)...

So the first thing we want is a Row Level measure for the SUM([Sales Count]) for each Item/Sub-Item/Hour combination (it looks like this is the grain of your data here, but just in case your "real world" situation has a finer grain, I've created an LoD to force the SUM at the level we want)

[Sales Count Per Item/SubItem/Hour - LoD]

{FIXED [Item],[Sub Item],[Hour]: SUM([Sale Count])}

Once we have this, we can use the Tableau Bin function to create our Bins (I've created a parameter, so you can control the bin sizes dynamically...and is currently set to 10)

Alternatively, I have also created "Human Readable Bins", using a calculated field...just copy the formula here  Human Readable Row Level Dynamic Bins (and change the fields to you ones)

STR(

IF (

(ROUND([Sales Count Per Item/SubItem/Hour - LoD] / [Sales Count Per Item/SubItem - LoD Bin Size]) * [Sales Count Per Item/SubItem - LoD Bin Size]))

< [Sales Count Per Item/SubItem/Hour - LoD]

THEN ((ROUND([Sales Count Per Item/SubItem/Hour - LoD] / [Sales Count Per Item/SubItem - LoD Bin Size]) * [Sales Count Per Item/SubItem - LoD Bin Size]) + 1)

ELSE ((ROUND([Sales Count Per Item/SubItem/Hour - LoD] / [Sales Count Per Item/SubItem - LoD Bin Size]) * [Sales Count Per Item/SubItem - LoD Bin Size]) - [Sales Count Per Item/SubItem - LoD Bin Size] + 1)

END

)

+ ' to ' +

STR(

IF (

(ROUND([Sales Count Per Item/SubItem/Hour - LoD] / [Sales Count Per Item/SubItem - LoD Bin Size]) * [Sales Count Per Item/SubItem - LoD Bin Size]))

< [Sales Count Per Item/SubItem/Hour - LoD]

THEN (ROUND([Sales Count Per Item/SubItem/Hour - LoD]/ [Sales Count Per Item/SubItem - LoD Bin Size]) * [Sales Count Per Item/SubItem - LoD Bin Size]) + [Sales Count Per Item/SubItem - LoD Bin Size]

ELSE (ROUND([Sales Count Per Item/SubItem/Hour - LoD] / [Sales Count Per Item/SubItem - LoD Bin Size]) * [Sales Count Per Item/SubItem - LoD Bin Size])

END

)

The next part is to combine the Item and Sub-Item fields, so I have a unique value for each Item/Sub-Item combination

[Item + SubItem]

[Item]+' '+[Sub Item]

and then I can arrange the pills as required, and use a COUNTD on [Item + SubItem]

and I'd also filtered out the NULL sales count

Hope that helps and makes sense, but let me know if not.

• ###### 3. Re: Bin Analysis

Thanks Simon. Let me check the solution and revert.

• ###### 4. Re: Bin Analysis

Hi Saravanan ,

you can create calculated field for bin analysis like below.

CASE [Sale Count] > 0

WHEN [Sale Count] >= 0 AND [Sale Count] <=20

THEN "0-20"

WHEN [Sale Count] >= 21 AND [Sale Count] <=40

THEN "20-40"

WHEN [Sale Count] >= 41 AND [Sale Count] <=60

THEN "40-60"

WHEN [Sale Count] >= 61 AND [Sale Count] <=80

THEN "60-80"

WHEN [Sale Count] >= 81 AND [Sale Count] <=100

THEN "80-100"

WHEN [Sale Count] >= 101 AND [Sale Count] <=150

THEN "100-150"

WHEN [Sale Count] >= 151 AND [Sale Count] <=200

THEN "150-200"

WHEN [Sale Count] >= 201 AND [Sale Count] <=500

THEN "200-500"

ELSE "0" END