From the below sample data table. I want to create an aggregational table and later I will convert it to the scatter plot which shows aggregation of the Products based on their sales amount and Actual amounts. I considered using the ranking for this approach but I am not getting the results as expected.
sample data table:
Average of TOP 3 Products by Sales Amount
Average of TOP 3 Products by Actual Amount
Average of TOP 3 Products by Sales Amounts: C,A,B are the Top 3 Products. Now I want Avg(Risk Value) and Avg(Return Value) of these Products.
Average of TOP 3 Products by Actual Amounts: B, C, D are the Top 3 Products. Now I want Avg(Risk Value) and Avg(Return Value) of these Products.
Expressions I am using:
if(aggr(rank(sum( [Sales Amount])),[Product ID])<= 3,'Average of TOP 3 Products by Sales Amounts',
if(Aggr(rank(-sum( [Actual Amount])),[Product ID])<= 3 ,'Average of TOP 3 Products by Actual Amounts'))
You are really a genius. I really appreciate your help today. The expression you shared worked as expected. One last thing i would like to share is. I also want to include my own products example: If(wildmatch([Product],'*Micro*'),[Product]). Is there a way that I can add this if condition to the valuelist?
I found the solution for this. let's follow the order.
I have 1000 Products, But only 50 that contains Micro. Now I want to show Avg risk value of top 3 by sales amount of 1000 Products and individual Riks values of products that contains Micro.
Solution to achieve this requirement is.
load * inline [
=pick(Dim,'Average of TOP N Products by AUM: 1 YR Risk and Returns','Average of TOP N Products by 1 YR Flows: 1 YR Risk and Returns','Average of Bottom N Products by 1 YR Flows: 1 YR Risk and Returns',