Hello, new to Quicksight but have got everything else in order except this nut, which is more difficult to crack.
I have a database with row that for simplicities sake have the following values: startDate, endDate, value. Here the value is one that actually takes place every day between startDate and endDate.
What I would like to achieve is have a line chart or bar chart that shows the sum of the values per month. My problem is that I can get the values for the exact startDate but how can I populate this value to the dates in between startDate and endDate?
Image for reference of how this data is populated in another system. The data is evenly distributed even though most of the rows reflecting this year start at Jan 1st -25.
Hi @jhznyman and welcome to the QuickSight community!
You mention that your value is one that takes place every day between start and end date; so does that mean a given row can be longer than one day? Are they all this way or do they vary in lengths?
Additionally, a couple questions regarding your value:
If a row lasts longer than one day, is the value essentially a combination of ‘each day’?
In the same case, would the value for each day be the same (For example, if a row stretches from 2/1/25-2/4/25 and your value was ‘16’, would that imply that the value for each day is 4?)
If you could share a screenshot of what you currently have setup in QuickSight, we can share notes on how to obtain your desired outcome!
Hi Brett, thanks for your reply! And good that you made me look into the data a bit more in detail. Found a flaw in my reasoning, though this issue still remains. This is basically resourcing/staffing allocations for our company. So how many hours we have allocated total and per customer in a given time frame.
The data looks roughly like this. But I would like to be able to massage it into displaying allocations for a time frame, say months in Q4 2024.
The rows can indeed be longer than one day, and with very rare exceptions are, and do vary in length. I will however correct that the value (calculatedAllocationHours) in the screenshot does not take place every day. It is actually the value divided by the number of days between startDate and endDate. Which I can get nicely with netWorkDays function.
So if a row does last say a work week (5 days) and the value is 25 i would like to display 5 per day. For one row the value per workday is the same.
I’m thinking if I might perhaps need a separate date table depicting workdays.
Hi @jhznyman,
You could explore using the netWorkDays function to help you with this. Use that to find the number or work days, then divide your calculatedAllocationHours field by that to find your per day allocation.
Let me know if this would work for your case or if you have any additional questions.
Hi @Brett, yes I can get the duration nicely with the netWorkDays function and get the daily allocation. The underlying issue is the dates though.
Let’s say I have one allocation with the following data:
startDate: Jan 15th 2025
endDate: Mar 15th 2025
calculatedDailyAllocation: 5h
Now I do get the daily allocation hours to show up on Jan 15th and on Mar 15th. How can I get it to also be present on the days in between? Say I wanted to check the allocation in February. I can’t figure out a way to populate that daily allocation to the dates in between startDate and endDate.
Hi @jhznyman,
Apologies for missing your last response until now! So after testing out a few different scenarios, I’m not quite sure this exact outcome is currently feasible within QuickSight. To plot out the data, you would need to have another date field that can handle the data being sorted. I agree that this type of calculation would be a nice addition so I’ll mark this as a feature request.
Let us know if you were able to find a work around that helped create something similar or if you have any additional questions regarding the topic.
Hi @jhznyman,
While I’ve marked this as a feature request, following up to check if you had any additional questions or comments.
If we do not hear back within the next 3 business days, I’ll close out this topic.
Hi @jhznyman,
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.
