1 Reply Latest reply on Oct 20, 2014 4:52 AM by Jonathan Drummey

# Use table calculation in another agregate calculation

Hi,

I am trying to do something a bit complicated, unfortunately I haven’t found any help about it on the forum. Can you please help me ?

You'll find attached my current work : Price band analysis - test

I am trying to segment products into several categories depending on their price and I want these categories to have the same size.

I have found a way to get the price range for each categories, but now I want to use it to compute automatically stats per categories.

The input file that I have (cf test.xlsx attached) contains the following columns :

Nom : name of the product

Price : its price

Sales : Number of sales

Contacts : Number of sales opportunity

Using table calculation, I managed to compute running percentage of sales, and to get the value of the running percentage of sales for the previous price value.

Then, I created variables (one for each break) who takes for value :

* the price :  when the running % passes a given threshold (1/nb of category for the first break for instance) for this price value

* Null : otherwise

I have one calculated field for each limit between categories (break 1, break 2, break3, ...) and the number of categories to create is a parameter (Nb_cat)

Then, I have a last field (Break) which is equal to "Break" is one of the break value is not null, and null otherwise.

Then, I create a table where columns = Price, Text = Break and I filter to keep only the non-null value of the field Break.

It gives me the price limits that split my sales into Nb_cat categories of equal size.

You can see this table on the sheet called "Break values", and the detailed calculation on the sheet "Explaination".

It is good, but I want to go further and compute stats per categories (as defined with these price limit). The problem is that I don't know how to automatically store these value somewhere (as parameter). I would to get something like Break1Param=max(Break1) or something like that, and then use it to attribute a category to each product :

if price < break1 then "Cat.1"

1. etc....

At the moment, the only thing I can do is creating parameters called b1, b2, b3, b4 and manually copy the values I get from the "Break Value" table. Then I can use these parameters in a calculated field (Category) to assign products to their category, and create a table (sheet "stats per categories") with Column = Category, and display some measures.

Unfortunately, the dashboard here is using dummy data, but the actual use case has to handle a very large set of data (and a much larger number of categories), which make any manual intervention incredibly painful (basically, if it isn't automated, it will be too painful to be useable).

Therefore, I'd like to find a way to automatically use the output of the table calculation to allocate products to their category and compute stats per category, without any manual intervention.

I look forward to receiving your solution. This is a complex but very interesting problem which has already taught me a lot of advanced Tableau functionality. We can really amazing thing with this product. Nice job !

Data-driven regards

Baptiste

• ###### 1. Re: Use table calculation in another agregate calculation

Hi Baptiste,

Are you still looking for help with this?

Jonathan