Calculated field not showing when combining datasets

Question & Answer
, ,
1

Hi -

Im new to Quicksight

In Amazon QuickSight, I have two datasets: Contact Record and Fiscal Calendar. The Contact Record dataset contains all the details, while the Fiscal Calendar dataset includes fiscal week, fiscal month, and fiscal year. To join these datasets, I created a calculated field in the Contact Record dataset to convert the disconnect timestamp into a date format that matches the Fiscal Calendar’s date format. However, when performing a left join, the calculated field does not appear in the field selection options. Why is this happening, and how can I ensure the calculated field is available for the join

What are the possible altetnatives so I can combine the two datasets?

2

Hi @JobertArue ,

Welcome to the QuickSight Community!

Few possible reasons for the calculated field not appearing in the Join:

  1. Excluded Calculated Field: Make sure the calculated is not excluded from the data set fields, you can ensure this by checking if the calculated field is showing up in the Excluded Fields section. If yes, click on the 3 dots next to the field name and then click include
  2. Save and Publish changes: Make sure to Save and publish the Contact Record dataset after creating the calculated field. Once the changes are published successfully, make sure the data is fully refreshed (in case you’re using SPICE) and then try to join the tables again
  3. Data Type Mismatch: Check if the data types of the joining columns are same. If not, make the changes to the calculation to ensure proper data types
  4. Formula Errors: Double-check the calculated field formula for any typos, incorrect function names, or logical errors. Try simplifying the formula temporarily to isolate the issue.

If these steps don’t resolve the problem, please share the calculated field for better clarity.

Regards,
Muhammad Zubair

3

Thanks.

Date in contact record is is like this 2025-03-05T08:01:08.00Z while in fiscal calendar 2025-03-05T00:00:00.00Z

The calculated field formula I used is

truncDate(‘DD’, {date})

I used this formula to remove the hours, minutes and seconds so it will match the date in fiscal calendar.

Data type are the same as DATE.

4

Hi @JobertArue

Will you be able to resolve the join issue at your end? Kindly let us know if you need any help.

If we don’t hear back within the next 3 business days, we’ll proceed with close/archive this topic.

Thank you!

5

Hi @JobertArue

Since we have not heard back from you, I’ll go ahead and close/archive this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for relevant information.

Thank you!