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

    Use table calculation in another agregate calculation

    Baptiste Dufresne

      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.

       

      Any idea about how can I do that please ?

       

      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