Hi, I’m looking to create a table or chart where sales people can see the dollar value of expiring contracts for a selected customer for each of the next 8 quarters. Some customers may only have one expiration event, and in that case I still want the table or chart to consistently show the next 8 quarters, even if that means showing seven zero-value quarters and one quarter with some dollars. My data is stored in rows with order number, customer number, contract start/end dates, dollar value etc.
The challenge I have is to get QS to always show the same 8 upcoming quarters, and not just the quarter/s that have expirations. My team tells me QS will always only show periods where there is a record of an expiring contract, so for example for a single-contract customer with a 2023-Q2 $525 contract expiration, there is no way to get QS to display the following:
2023Q1: $0 | 2023Q2: $525 | 2023Q3:$0 | (…and so on until 2024-Q4…) | 2024-Q4: $0
QS will only show one column/period of 2023-Q2: $525. Unless, we add some type of dummy data which we want to avoid.
Any ideas of good or bad ways to solve this would be greatly appreciated!
Are you able to share a screenshot of what’s currently showing? Based on your description there should be an easy solution. Do you have any filters applied that are causing the visual to not display values containing a zero?
You want your axis to show Q1, Q2, Q3 and Q4. If you only have data for Q1 and Q2, by default QuickSight won’t show Q3 and Q4 on the axis.
If your gap is in the middle, you can select “Show date gaps” option. For example, if you have data for Q1 and Q4 and you select the option, QuickSight will add Q2 and Q3 to your axis.
The problem is when the missing data is at the beginning or at the end, e.g. if you have data for Q2 and Q3 but not for Q1 and Q4. In this case QuickSight can’t show the gaps. The only way is to join with a table that contains all dates starting from the oldest date in your data to the current date during data preparation.
Hi Todd, this is a mockup of what I want to show for a customer with one $400 contract for Product 1 expiring in Q223 and a $200 contract for Product 2 expiring in Q424. Like David_Wong mentioned in his response, my issue is in showing the quarters before or after this customer’s first/last record. When trying a customer with contracts like the screenshot in QS, only Q223-Q423 would show up.
Are the values in the dataset 0 or are they NULL?
Thanks David, you’re exactly right in what I struggle with. When you say “join with a date table during data preparation”, could you add a little more detail on how to join and then use the table to achieve the end result of always showing 8 upcoming quarters of expirations?
I think they would be null. The raw data for Product 1 in the screenshot example could look like this (only one row with the following columns/values). And the expiring quarter can be calculated based on the contract end date being after the first date of a quarter and the contract end date being before the last date of that quarter.
Customer nr: 987654321
Order nr: 123456789
Dollars: 400
Contract start date: 2022-04-15
Contract end date: 2023-04-14
What I meant is that you need a Date table like this that covers the entire range of dates present in your data. Ideally this should come from your data source (e.g. database) but if that’s not possible, you can create it manually in Excel and update it with new dates are required. Create a dataset using this Excel file.
Add a Date Key column to your Orders dataset like this:
Then create a full outer join between your Orders dataset and the Date dataset using the Date Key column. You’ll get something like this:
Finally create your visual but instead of putting the Contract End Date column in it, put the new Date column. In this screenshot I’m only showing a few dates before your date and a few after but basically on the dates when you have data, you’ll have a value in all the columns. On dates when you don’t have any data, you’ll see NULL. All the dates found in your Date dataset will be on your x-axis.
Thank you David, I think this will help.
