Tableau to Quicksight Calculations

Question & Answer
, , , , , ,
1

Hello Quicksight Users,
I am working on a Tableau to Quicksight migration and I am currently stuck on a calculation.First there is a field to calculate the latest date:

{FIXED [ID]:MAX([Reporting Date])}

I calculated this in quicksight as

max({reporting date}, [{id}])

The next calculation uses the latest date to calculate latest flag

– Tableau {FIXED [ID]:MAX(IF [Latest Date]=[Reporting Date] THEN [Flag] END)}

I then ran into an issue when I tried to calculate because of a mismatch aggregation error

ifelse({Latest Date}={reporting date}, {flag}, NULL)

After this, another function uses the latest flag for a calculation

COUNTD(if [Latest Flag]=1 then [id] else null end)

There’s been different things that I’ve tried for latest flag but once I get here there’s an issue because of mismatch aggregation again. I’m sure there’s a simpler way to do this, I even tried to do it on the dataset level but the calculations aren’t matching up. Any help is appreciated! (edited)

2

To replicate the Tableau calculations you’ve described in Amazon QuickSight, you can follow these steps:

  1. Calculate the Latest Date:
    The Tableau expression {FIXED [ID]:MAX([Reporting Date])} can be translated to the following QuickSight calculated field:
MAX({reporting date}, [{id}])

This will give you the latest reporting date for each unique ID.
2. Calculate the Latest Flag:
The Tableau expression {FIXED [ID]:MAX(IF [Latest Date]=[Reporting Date] THEN [Flag] END)} can be translated to the following QuickSight calculated field:

CASE
  WHEN {Latest Date} = {reporting date} THEN {flag}
  ELSE NULL
END

This will create a flag that is equal to the original flag value when the reporting date matches the latest date, and NULL otherwise.
3. Calculate the Count of IDs with Latest Flag:
The Tableau expression COUNTD(if [Latest Flag]=1 then [id] else null end) can be translated to the following QuickSight calculated field:

COUNT_DISTINCT(
  CASE
    WHEN {Latest Flag} = 1 THEN {id}
    ELSE NULL
  END
)

This will give you the count of distinct IDs where the latest flag is 1.

To address the “mismatch aggregation error” you’re encountering, make sure that the granularity of your calculations matches the granularity of your visual. If you’re still facing issues, try the following:

  1. Ensure that the data types of the fields involved in the calculations are correct.
  2. Check if there are any null or invalid values in your data that might be causing issues.
  3. Try breaking down the complex calculations into smaller, simpler steps to isolate the problem.
  4. Consult the QuickSight documentation or reach out to the QuickSight community for further assistance.

Remember that the syntax and function names may differ slightly between Tableau and QuickSight, so you may need to adjust the expressions accordingly. Let me know if you have any further questions or need additional help.

1 Like
4

Hi @johndude,
It’s been awhile since we last heard from you, did you have any additional questions regarding your initial topic?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

1 Like
5

Hi @johndude,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!