Area Line chart - Display Top N items by default but allow adding any selected item

[soham]

Hello,

I am building an area line chart in QuickSight and I want to implement the following behavior:

• By default, the chart should show the top 2 SKUs

• At the same time, the user should be able to search/select any SKU, and that SKU should also appear on the chart even if it is not in the top 2.

Current Setup

Date
Dataset-level field used on the X-axis.

Brand SKU

concat(Brand, ' - ', {Product Name}, ' ', '(', SKU, ')')

SKU Rank

rank(
    [sum(units) DESC],
    [Date]
)

What I Tried

I created a parameter linked to the SKU field and attempted to build a calculated field like this:

ifelse(
    {SKU Rank} <= 2 OR {Brand SKU} = ${SKUSelector},
    1,
    0
)

However, this returns the following error:

“Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields.”

This happens because:

• {SKU Rank} is an aggregated/table calculation

• {Brand SKU} is a dimension (non-aggregated)

Desired Behavior

Default chart:

Top SKU 1
Top SKU 2

If user searches/selects a SKU:

Top SKU 1
Top SKU 2
Selected SKU

Question

We want similar pattern in QuickSight where:

• Top N items are shown by default

• but users can dynamically add any selected item to the same visual

without running into aggregation mismatch errors? Any workaround using parameters, filters, LAC/table calculations, or alternative design patterns would be greatly appreciated.

Applying the SKU Rank <= 2 filter removes all other SKUs from the visual, so even if a user searches/selects another SKU, it does not appear because it has already been filtered out

Additional Question (Tooltip Ordering)

Currently, the tool-tip shows multiple SKUs but not in rank order. For example:

Is there any way to make the tool-tip appear sorted by rank, such that it displays:

SKU    Rank
A       1
B       2
C       3

when hovering over a date point?

Thanks

[DylanMorozowski]

Hello @soham, there are a few things we will need to do in order to make this work.

First, we want the rank calculation to instead be a denseRank function that utilizes the PRE_AGG or PRE_FILTER aggregation. That will allow you to avoid the mismatch aggregation error. It will look something like this, but we may need adjustments depending on the group by you want for the units sum function.

Rank SKU = denseRank([sumOver({units}, [{Brand SKU}, {Date}], PRE_AGG) ASC], , PRE_AGG)

Now, your ifelse statement should work a bit better. I would recommend that you utilize a Free-form layout for the sheet so that you can show and hide the SKU selector dropdown depending on if the SKU selector option is selected by the user. This can be done with conditional rules when using that layout.

Then, you calculated field for filtering can look something like this:

SKU Filter = ifelse({Rank SKU} <= 2, 1, in({Brand SKU}, ${Selected SKU}), 1, 0)

Now, you can apply the SKU Filter calculated field as a filter on your visual. You can set it to only return values that equal 1 and make sure to exclude NULL values in the dropdown. We may need to make some slight adjustments depending on how you are managing the SKU Selection, but this should get you closer!

As for the tooltip ordering, you can try adjusting the sort of the visual with a calculated field ( you can use an off-visual field) to see if you can get values to return the way you want.

[soham]

Hey,

Thank you so much for the quick response — it really helped me get close to the desired behavior.

I just want to confirm one last requirement.

Expected behavior

• Default view: show Top 2 SKUs

• If a user searches/selects any SKU manually: show only the searched SKU(s) in the graph

Current behavior

Right now, the setup is working almost as expected, but when a user searches for an SKU, the graph still shows:

• Top 2 SKUs

• + searched SKU

Whereas I want it to show:

• searched SKU only

Current setup

Rank SKU

Rank SKU

denseRank(
    [sumOver(units, [{Brand SKU}, Date], PRE_AGG) DESC],
    [Date],
    PRE_AGG
)

Top SKU Rank + Others

ifelse(
    {Rank SKU} <= 2,
    1,
    ifelse(
        in(SKU, ${SKUSelector}),
        1,
        0
    )
)

This field is applied as a visual filter with:

= 1

${SKUSelector} is multi-value parameter connected to SKU field from the dataset.

Question

Is there a way to adjust this logic so that:

• when no SKU is selected → it shows Top 2

• when SKU(s) are selected in the selector → it shows only those selected SKU(s) and does not retain the default Top 2

Thanks again for the quick help.

[DylanMorozowski]

Hello @soham, how are you managing the default value for the SKU selector? We basically would need to check for that default value and only display the top 2 when that is displayed.

Then, you can adjust the filter calculation and manage this:
ifelse({Rank SKU} <= 2 AND in(“Default Value”, ${Selected SKU}), 1, in({Brand SKU}, ${Selected SKU}), 1, 0)

You don’t need the 2nd ifelse statement inside of the main ifelse statement. You can have multiple conditionals within a single ifelse calculated field. With this change it will only return the top 2 rank values when no other SKUs are selected.

Also, since that SKU parameter is multi-value, we can only check the values returned with the in() function. Does you dropdown include an option for Select all? If so, we need to add one more condition in the filter. When Select all is used, the parameter will actually return NULL instead of the individual SKU values. Here is the updated filter calculation:

ifelse({Rank SKU} <= 2 AND in(“Default Value”, ${Selected SKU}), 1, in({Brand SKU}, ${Selected SKU}) OR in(NULL, ${Selected SKU}), 1, 0)

[soham]

Hello @DylanMorozowski

Thank you so much for your quick response, very much appreciated - I was able to achieve the expected output with few changes -

This is how filter field looks like -

ifelse(

    maxOver(

        ifelse(in(SKU, ${SKUSelector}), 1, 0),

        [],

        PRE_AGG

    ) = 1,

    ifelse(in(SKU, ${SKUSelector}) OR in(NULL, ${SKUSelector}), 1, 0),

    in('Default_top2', ${SKUSelector}) AND {Rank SKU} <= 2,

    1,

    0

)

The maxOver part is the key that detects whether the user has selected any real SKU. Without it, the formula cannot distinguish between:

• Default state (Default_top2)
• User-selected SKU(s)

Issues/Observations -

1. The previously selected SKU remains in the parameter state. It seems the parameter value persists internally even when the control is reset. (I think we will need to refresh the parameter from three dots instead reset?)

2. Search sometimes does not return a valid SKU

In some cases:

• After selecting an SKU (e.g., LTTV-39)

• Searching for another SKU (e.g., DLE6100W)

The search does not return the value even though the SKU exists in the dataset.

This occurs even when:

• The SKU exists in the dataset.

It went like this: I manually entered 2 SKUs, and when I try to enter a third one, it doesn’t work.

3. If a SKU is not present for the selected combination when searched, the formula should return “No data available” and should not fall back to the default Top 2 SKUs. Currently, this is not happening. For example, SKU DLE6100W is not present under the selected combination, but instead of showing “No data available”, it is returning the default Top 2 SKUs (highlighted in yellow).

Please Suggest workaround on above issues.

Thanks Again for the efforts, really appreciate it! .

[soham]

Hey @DylanMorozowski

Any update on above issue?

Thanks.