I have a Utilization table that shows the percentage of hours our employees have logged divided by the total hours possible. This table shows the utilization rate per month for the past 12 months. When I add filters to show just one month, it shows the correct amount based on my calculations from the data, but when I change the filter to show from June '23 to June '24, it inflates some of the months utilization rate. Is that a bug? Is that something I can fix? Please let me know if anyone else has experienced this or has a solution. Thanks!
Hi @doneliza and welcome to the QuickSight community!
I wonder if when you expand the filter range, it’s affecting your calculation. Could you either share the calculated field that you’re using for this instance or upload your analysis to Arena so that we can view how your data is being affected.
Thank you!
It’s( (working hours + support hours))/((# of workers x 168)-(PTO Hours))
Hi @doneliza,
Thank you for sharing! I think it will be a bit easier to assist through Arena so that I can see how your filter is setup and how the table is reacting!
Could we do a call or is there office hours I could be signed up for? I’m not sure if Arena would work with some of the data being relatively high confidentiality.
Hi @doneliza,
Totally understand, unfortunately there’s no call in option for community. Maybe we can work around this if you share some screenshots instead and black out the confidential information portions. Ideally, a picture of the visual, the values being used for the visuals and the filters affecting the visual and I will do my best to assist further!
Included screenshots of the filters and different amounts. The first is correct. The second is inflated. Please let me know if you have any thoughts. Thanks!
Thanks for sharing @doneliza!
Is the calculated field you sent yesterday being used for pentesting hours? It could be helpful to see that calc. field as well.
My initial thought is that you will want to try utilizing LAC-W aggregations and create a month date field with truncDate to ensure you are always calculating for the right time frame
Date Month: truncDate(‘MM’, {Date})
Pentesting Hours: sumOver({pentest}, [{Date Month}], PRE_AGG)
I am curious if the returned values for the SumOver function would work across both of the applied filters.
The issue could also be due to utilizing relative date4 filters along with exact date filters. Some portions of the time period may be excluded or included. I would recommend using the same type of date filter for comparisons.
One last note; looking at your screenshots, I noticed some differences in the June 2024 numbers. For instance Pentesting hours, support hours, billable hours and PTO hours are different than they are in the single month screenshot. Were these two screenshots taken at the same time? If so, we may need to look a bit closer at those since there are discrepancies.
No sorry this is the calculated field for pentest hours;
sumIf(hrs, {hour type: name}=“Task Time” AND (project<>“Pentest Support Schedule”) AND ({hour type: name}<>“Non Pentest Time”))
Should it be sumOver rather then sumif?
So yes exactly. That’s what we can’t figure out. When we go through the data from the dataset we get 74.1% that is correctly shown in the just June ss. When we expand the filter to include all months, it inflates the June utilization and we don’t know why. Any advice you have would be helpful. Thanks!
Hi @doneliza,
It may be worth trying out the SumOver to see if that adjustment helps.
It would look something like this:
sumOver(ifelse({hour type: name}="Task Time" AND (project<>"Pentest Support Schedule") AND ({hour type: name}<>"Non Pentest Time", {hrs}, NULL), [{dateMonth}], PRE_AGG)
*Note: You’ll need a date month calculated field to reference in the sumOver partition (as seen above):
dateMonth = truncDate('MM', {Date})
Also, when using the SumOver, you’ll want to set to a ‘Min’ or ‘Max’ aggregation in the field well.
Made a dateMonth and updated to the above sumOver and it worked but then the whole graph disappeared due to an error. I think it’s making the numbers too big or too small. What is a min or max aggregation? And what’s a field well? Maybe that was the issue?
Hi @doneliza,
The field well would be the column that opens up when the visual field options. If you click on the ellipses next to the value in question, you can change how you’d like to aggregate it.
Let me know if this works!
Hmm it’s not a sum but “custom” so I can’t seem to make it min or max. I tried to for the “show as” but that didn’t seem to work. Any other suggestions? It’s accepting the “Sum over” calculated field but then the graph is throwing an error.
Is there an equation for
The total per separate month of {hour type: name}=“Task Time”
but excluding both the project<>“Pentest Support Schedule” AND {hour type: name}<>“Non Pentest Time”)
I also have a calculated field for dateMonth=truncDate(‘MM’, {timesheet:end date})
Hi @doneliza,
Could you send a screenshot of the sumOver calculation that you used above, sumOver should not return ‘custom’ option in the field well aggregation so I just want to double check the calculation you typed out. Also, is the hrs field a calculated field or is it directly from your dataset?
I noticed there was an extra parenthesis in the calculation I typed out above, so I will put the updated version below:
sumOver(ifelse({hour type: name}="Task Time" AND project<>"Pentest Support Schedule" AND {hour type: name}<>"Non Pentest Time", {hrs}, NULL), [{dateMonth}], PRE_AGG)
Make sure you do not wrap the {hrs} field in the return value of the ifelse statement in a sum, return is as is. If this doesn’t help, please send over the calculated field you have written out and we can try to find an alternate solution.
Thank you!
hrs is from the dataset. Not a calculated field. It is the number of hours listed per {hour type: name} so for example it would show 4 hrs have been charged to {hour type: name} = task time.
Hi @doneliza,
Thank you for sharing, so how does that calculated field look in your field well, could you share of screenshot of that as well.
The last calculated field I sent over, does that not provide the output you need? Could you share screenshots of how that looks as well in comparison to what you need?
Thank you