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.

[soham]

Hello @DylanMorozowski

Do we have any updates on the issues mentioned above - specifically the first TWO ? The calculation seems to be working fine; it appears the issue may be related to parameter behavior (This doesn’t happen consistently—it only occurs sometimes)

I also tried the approach below, but the same issue still persists.

I thought its happening because of Default_Top2 to be present under SKUSelector parameter , so i removed Default_Top2 from the SKUSelector parameter and created a separate ViewMode parameter with Top2 and Manual as drop-down values. With this setup, Top2 mode will display the default top 2 SKUs, while in Manual mode, searching or selecting an SKU in SKUSelector will display the searched/selected SKU. The calculated field will be used as a visual-level filter and applied with the condition = 1

ifelse(
    ${ViewMode} = 'Top2',
    ifelse({Rank SKU} <= 2, 1, 0),

    in(NULL, ${SKUSelector}),
    1,

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

But the same issues still persists (first TWO issues related to parameter)

Reset/refreshing the control would be the option if this happens?

Any update on this would be really helpful and much appreciated .

Thanks.

[DylanMorozowski]

Hello @soham, my apologies for the delay. I can try to help you resolve these remaining questions.

First of all, we can simplify this statement and I believe we can remove the parameter value issue. One thing to note, you do not need so many ifelse statements nested together. You can use as many if/else if/else statements in a single ifelse calculated field. Mainly, we need the ViewMode = ‘Top2’ to decide how the filter will work.

ifelse(
${ViewMode} = ‘Top2’ AND {Rank SKU} <=2, 1,
${ViewMode} = ‘Manual’ AND (in(SKU, ${SKUSelector}) OR in(NULL, ${SKUSelector})), 1,
0)

The main issue that your previous calculation was missing was checking for the ‘Manual’ value in the ViewMode parameter. It was going to check was SKUs were selected no matter the mode the user had selected. This should resolve that issue.

As for the search doesn’t return a valid SKU, what control dropdown type are you using? List, Dropdown, or Text? My initial thought is that the search might be case sensitive so it may not show if all casing/spelling doesn’t match exactly. Also, how many SKUs are you expecting the dropdown to display?

I believe my updated calculated field will fix the issue of reverting back to the defaulted view instead of displaying “No data available” when no matching SKUs are selected in the control dropdown.

[soham]

@DylanMorozowski

Thanks for the response.

At this point, the calculation logic is sorted. Both the original method (as stated on Mar 16) and the ViewMode approach are working correctly from a filtering standpoint. The issue we are facing is specifically with the SKUSelector parameter/control in QuickSight.

We are currently seeing two issues with the SKUSelector control:

1. Previously selected SKUs persist in the parameter state, even after using Reset. The issue temporarily resolves after using Refresh from the control menu, which suggests the parameter state is not fully cleared by Reset.
2. Search does not always return valid SKUs.
   After selecting one or more SKUs, searching for another SKU sometimes fails even though it exists in the dataset. In some cases, the control appears to only search within previously selected values instead of the full dataset.
   as shown below -

We are working with a large SKU dataset (thousands of SKUs), so users rely primarily on the search functionality rather than scrolling through the dropdown.

This is how the parameter setup looks like -

This is how current filter field looks like -
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)

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

)

Thank you so much for the revert, much appreciated !!

[DylanMorozowski]

Hello @soham, is the dataset you are using running on Direct Query or is it stored in SPICE? If it is Direct Query, I could see there being a potential issue loading in the list properly. Maybe it is hitting a limitation on the query time and impacting performance of the search.

Another thought. I know the SKU value was being generated by a calculated field. Are you using a calculated field on the dataset level to create the field, or is it being calculated in SQL or pulled directly from the dataset? There is also the potential of hitting some kind of limitation around searching from a calculated field vs. a native dataset field.

One other thing I can think of is to try and filter down the list of SKUs to make searching a little easier. Is there a group by field that you can add a dropdown for to reduce the number of SKUs in the control the user selects from? If the user could select a value for a field like client name, city, etc. then the list of SKUs could be dropped to the 100s instead of 1000s, your search functionality should be improved. You can use the “Show relevant values” button on the SKUs dropdown to make it display values related to the other filter.

[soham]

Hello @DylanMorozowski

1. Dataset I am using is stored in SPICE.

2. From the formula below, the SKU field—linked to the SKU selector parameter—is a dataset-level field, while the remaining fields are analysis-level calculated fields

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

)

3. I’m already using the “Show relevant values” option on the SKU dropdown to display values based on other filters. It seems the only way forward is to further restrict the dropdown to more relevant values and reduce the control size.?

If there aren’t any other options, do you think we could raise this as a feature request to improve visibility further? It doesn’t seem to be a calculation or logic issue—more like the tool’s internal behavior

Thanks for the quick revert.

[soham]

Hey @DylanMorozowski

Let me know your thoughts on my above comment.

Thanks.

[DylanMorozowski]

Hey @soham, my apologies for the delayed response. It does seem like the only decent path forward is to try and reduce the number of options as much as possible through the show relevant values feature. It still seems odd though that the search wouldn’t work. I do wonder if there is some kind of limitation on the search based on case sensitivity or potentially some hidden characters in the field. It should search the fields properly even if the list is really long.

If there are no further questions, @Brett can you please mark this as a feature request?

[Brett]

Not a problem at all, I’ll mark this as a feature request!

[soham]

@Brett and @DylanMorozowski

Thanks for tagging it as a feature request.