How to create a calculated field based on date

Hello everybody! :sun_with_face:

I’m a cybersecurity professional and I’m exploring Quicksight. I am currently facing the challenge of recreating a control panel that has been deleted.

One of the requirements is to have information about “items_produced” and “machine_family”, which are stored in separate tables for each case.

I would like to create a calculated field that separates the production by “machine_family” and “produced_items”, limiting the results by month. For example, I want to view data for March, April, and May. Each month is a calculated field.

Does anyone know if it’s possible to accomplish this task using the “periodToDateSum” function or some other Quicksight function? Thanks for any help!

Hi @jjardel-cybersec

Will try to assist you here - currently not 100% clear on the challenge.

If you want to include calculations from BOTH tables in one Visualization we need to JOIN these two tables in a way that allows us to get the calcs we need in the dashboard.

You could either join before loading in QuickSight (using SQL in Athena for example) OR you could join directly in the QuickSight Data Prep. The type of join and logic depends on the structure/grain of the two tables and the logic that relates the data in them. Based on the names of them sounds like items produced is a fact while machine family is a dim providing additional details on products produced. Based on this simplest approach would be to join directly in QuickSight.

Once there, as long as you have a date field in your data model, you should be able to visualize production data by month grouped by machine family.

Please let me know if above helps or what additional questions it creates.

thanks

2 Likes