Error because of aggregated calculated fields cannot be used as filters in QuickSight

Q&A
1

I’m trying to create a filter in QuickSight based on a ranking/sequence value (for example, a calculated week index that ranges from 0–200),
calculated field of that is → dateDiff( {date field },maxOver({datefield}, , PRE_AGG), ‘DD’ ) / 7

The problem is that this ranking logic requires aggregation, and Quick Sight does not allow aggregated calculated fields to be used directly as filter values. When I try to apply the filter, QuickSight returns an error and the filter cannot be used as intended, attached screenshot of error

image
image314×177 21.1 KB

What is the recommended way to handle this scenario in Quick Sight

2

Hi @Pooja.Nandika ,

welcome to the community :tada:

Could you try the following?

  1. Keep your aggregated field as-is (the maxOver() version).

  2. Create a parameter (numeric).

  3. Create a calculated field without aggregation that references the parameter, e.g.:

include_flag =
  ( dateDiff({date field}, maxOver({date field}, , PRE_AGG), "DD") / 7 )
    = ${weekIndexParam}

  1. Use this boolean field in a visual filter (include_flag = true).

  2. Bind the parameter to a control on the analysis.

This could work because QuickSight allows aggregated expressions inside filters if the comparison is boolean and not directly exposed to a filter control.

As alternative you could precalculate the you original field in the datasource.

Please let me know if one of the mentions solutions work for you!

Best regards,

Nico

Did my answer help you in resolving your request? If yes, I would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you!

3

Hi @Nico

Thanks for the response,

I need the filter to be a multi-select dropdown, but with the current workaround (parameter + boolean include flag), I’m unable to get a multi-select option
I’ve attached a reference image showing the required multi-select dropdown format

image
image224×484 4.6 KB

4

Correct, then the parameter solution does not work.

The problem is that this ranking logic requires aggregation, and Quick Sight does not allow aggregated calculated fields to be used directly as filter values.

Does the aggregation depend on other filters, or could the ranking be done in the data source (database, custom SQL or prep flow)?

Best regards,

Nico

5

I added the following logic in my backend SQL:

SELECT *,
CAST(
DATEDIFF(
DAY,
date_field,
(SELECT MAX(date_field) FROM data_source)
) / 7 AS VARCHAR
) AS weeks_difference
FROM data_source;

In the query output, the weeks_difference values correctly range from 0 to 221, based on the week difference calculation.

However, when I use the same date field inside a QuickSight pivot table, the values no longer match what I see in the SQL output. The pivot table is showing mismatched values,

What is recommended way to get the required output in pivot table

6

Hi @Pooja.Nandika ,

when your backend SQL shows the right output, but Quick Suite does not show the data, maybe there is an issue with your data type. Can you change it manually in your dataset to string?

Best regards

Nico

7

Hi @Pooja.Nandika ,

Just checking back in since we haven’t heard from you in a bit. I wanted to see if the guidance shared earlier helped resolve your question, or if you found a solution in the meantime.

If you still have any additional questions related to your initial post, feel free to share them. Otherwise, any update you’re able to provide within the next 3 business days would be helpful for the community.

Thank you

8

Hi @Pooja.Nandika ,

Since I haven’t received any further updates from you, I’ll treat this inquiry as complete at this time. Please feel free to create a new post if you have more questions.

Thank you