Hello folk.
I’m from amazon learn team. I’m trying to build a Dash Board for Top N / Bottom N Trainings taken every week. I know how to send parameter to get number of trainings to display. However I’m wondering if there is a way to show either Top Or Bottom trainings Based on user selection in one chart instead of having two separate charts one for Top N and other for Bottom N? See the attachment to get a better Idea on what I wanted to achieve.
You can use Free-form layout and then use Rules to determine when a visual is displayed. There’s an example in demo central here
If you’re not familiar with Demo Central, checkout the help here
you may also consider using function “denseRank”
step1: create 2 parameters. one for selecting “Top or Bottom”. another one for the top or bottom number
step2: create 2 calculated fields. One for ranking the dimension. in this example, I am using Sales by Industry. Use ifelse condition to check user input parameter and group all Industry lower/higher than user input number(e.g. 5) into ranking “999”
step3: Create another calculated field to group dimension with ranking “999” into “Others”. In this example, I am trying to put those Dimension with ranking 999 into others. However, in your case, you may filter out those “999”
step4: create a chart to show the dimension and sales. I am using a table as an example. you may use line chart to do the same
you may also refer to example in here. This example is only showing “Top”. You may follow the above steps to create Top/Bottom https://democentral.learnquicksight.online/#Dashboard-TipsAndTricks-Calculation-Dynamic-Others-Group
Scrips for step2 :
ifelse(
${TopBottom}=“Top”,
ifelse(
denseRank([sumOver(Sales,[Industry],PRE_AGG) DESC],[],PRE_AGG) <= ${TopOrBottomN},
denseRank([sumOver(Sales,[Industry],PRE_AGG) DESC],[],PRE_AGG),999
),
ifelse(
denseRank([sumOver(Sales,[Industry],PRE_AGG) ASC],[],PRE_AGG) <= ${TopOrBottomN},
denseRank([sumOver(Sales,[Industry],PRE_AGG) ASC],[],PRE_AGG),999
)
)
Scripts for step3:
ifelse({Sales rank by Industry(TopOrBottom)} = 999, ‘Others’, Industry)
