2 Replies Latest reply on May 17, 2017 9:52 AM by Christian Schwehm

# Dynamic Groupings based on percentiles

Hello,

I cannot manage to get a dynamic selection of products based on a score and calculate the weighted Price of the selected products (in three terciles) over time.

The workbook shows how far I got:

In the basis worksheet you can see the individual data - 10 products that have a price, a score and a weight for different months.

I now want to select the top tercile of the products in each month based on the Score. For these selected products I want to calculate the weighted price. The problem is that the best products differ over time and I only managed to select the best products over the whole period, which assigns the products to the very same tercile over the entire period.

In fact in the given example, I would like to select for the first month product 1 and 7, for the second month product 6 and 7 and for the third month product 1,2 and 7. Their prices should be multiplied with the weight of the products in these months and this should be divided by the sum of the weights of the selected products resulting in a weighted price.

In the end I'm interested in getting the weighted price for the three terciles?

Is this even possible?

Christian

• ###### 1. Re: Dynamic Groupings based on percentiles

Hi Christian

I'm still not sure about how to show weighted price (each name or monthly average/weighted of weighted)

Anyways, tried to separate group by name x month.

Could you explain how you want to show weighted price?

In the end I'm interested in getting the weighted price for the three terciles?

Thanks,

Shin

1 of 1 people found this helpful
• ###### 2. Re: Dynamic Groupings based on percentiles

hello shinichiro,

thanks for the elegant grouping, that works fine. The weighted price might need now an LOD on this level, but i don't get that working. Let me try to explain what i'm after:

for each tertile i'm interested in the weighted price in each month so i could plot a viz with those three lines displayed over time. As all products start at 100 each weighted tertile would also start at 100. For the second month the weighted price would be the sum of the prices of the three products in tertile one multiplied by their weight in that month divided by the sum of the three weights. Is there a way to get the different levels of aggregation to work?