hi ,
I’m working on a quicksight dashboard in order to have the rate of each process per shift . I have the correct data to get the rate but the balance_date is only on day . Do you know how can I do to get the hours ?
Hello @juditliz , welcome to the Quicksight community!
This will depend on your data and what you are hoping to do. For visuals, in the field well or on the dataset panel you can change the aggregation of your date field by clicking the three vertical dots next to the name of the field:
To use that aggregated date in your calculated fields you can use functions like truncDate.
Let me know if this is what you were looking for!
Hi @duncan , thank you so much for your response! However, it’s not exactly what I’m looking for. Perhaps my explanation wasn’t clear enough. Let me try to clarify:
My data is structured as follows:
- I have the rate data in the “Rate” database, which includes rates for each process and function.
- However, the data doesn’t include division per shift. That’s why I’m joining it with an Excel file named “Shift Day and Time.” My join key is the day of the week.
The issue is that when I join based on the day, it doesn’t take into account the hours, so it doesn’t divide by shift. The smallest granularity in the rate data is per day.
Here’s a breakdown of my “Rate” table and my “Shift Day and Time” table to help better understand :
Shift Day and Time :
Rate :
And here’s the result I’m getting:
This is what I want to achieve, but with realistic values. Currently, it’s not calculating the number of hours per shift.
Therefore, I’m wondering if there’s a dataset that contains all the parameters I need, including volume, processed hours, process names and functions, and shifts. I don’t see any other way to complete my dashboard.
Thank you for your assistance; it’s greatly appreciated!
Hello @juditliz !
This will depend on the tables that you can pull from the data sources from your organization.
To me it looks like you will want to perform that join on a different field so that the shifts and days are appropriately grouped per row. Do you have any kind of unique key or ID value that matches on both tables?
Hello @duncan,
I hope you’re doing well. It seems that I might need to completely change my approach, as you mentioned. However, the challenge I’m facing is the absence of any unique key or ID value that aligns between both tables. I’ve also attempted to link the tables using an Excel file containing relevant data, but unfortunately, it didn’t yield the desired results.
Do you have any suggestions on how I can proceed further?
Thank you for your assistance.
Hello @juditliz !
No problem! I suppose it doesn’t have to specifically be an id or unique key, but some kind of unique attribute per row.
If you do not have control over the base data source to apply a unique id or attribute I would reach out to the person in your organization that does to make that change.
Hello @duncan,
I appreciate your support; it has been very helpful. Could you please assist me in connecting with the person responsible for the data source in my FC? Unfortunately, I don’t have direct control over it. My FC is FR-ORY4.
If I understand correctly, I need a unique attribute per row for my data in aftbi_ddl.d_daily_ppr_aggregates to effectively join it with the shift_pattern data. Am I correct in this assumption? If so, how I can achieve this?
Thank you for your guidance.
Hello @juditliz !
Unfortunately, I won’t be able to assist you because I am outside of your organization. The Quicksight community is a global forum for users to be able to post their questions. You will need to reach out internally at your organization to get data access.
I hope this helps!