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"
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 !