i’m using Quicksight to create a BI for the Sales Department of my company. I aim to create a calculated field that shows the quota fulfillment and aply that on a KPI and on a Table that shows the information by Branch, Product Group and Date.
The problem i’m having is that both the informations, of Sales and Quotas, are in 2 different Redshift databases. I’ve have tried to join them by using the Date field, but the Time of the dates didn’t match because ther’s only one Quota per product group and branch for a date while there can be many sales of that combination in the same date. One of my coworkers tried to combine both bases outside of Quicksight but it ended up copying the Quota value for every sale registered, returning the wrong value when displaying the Quota.
Is there a way in Quicksight to join both databases so i can perform the necessary calculation and also show both values (Sales and Quota) side by side, by the granularity explained before.
Bellow this question i will post mocked versions of both databases. Obs: The ‘branch_id’ and ‘center_id’ fields are the same information.
a. How to join the two tables
Since the time portion of your datetime column is not relevant for your analysis you could truncate that part so you get a date. I see there is a date_trunc function where you can apply the truncate option as ‘day’ and that will take out the time part. You can do this as you bring in the table into QuickSight SPICE dataset.
b. Granularity issue since Sales has more detailed data, but quota is at a higher level of detail
While there are ways using calculations to avoid the effects of duplication, my suggestion is to do it at the root by aggregating your sales data at the same level as your quota table and then perform the join.
Did the previous response help to resolve the issue you are facing? If so, please mark it as a solution. Otherwise, let us know what problems are persisting and we can help guide you towards a resolution.
If we do not hear back from you in 3 days, I will close out this topic. Thank you!
Hi @Henirque_Borges,
Since we have not 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.
