Can I merge or combine the number of instances an activity type as been used (Tech Issue) rather than every 15 minute interval being counted as one count? E.g If an advisor has Tech Issues 10:00 - 10:30, currently this is being counted as two because there are two ‘15 minute’ intervals in the half hour. I would like the count to be one with the duration time of 30 minutes.
Hi @LParkes and welcome to the QuickSight community!
This may depend a bit on how your dataset is setup. Would you be able to share some additional information, like the fields being utilized and the type of visual you’re trying to create?
Some screenshots may be helpful as well.
1 Like
Hi Brett, thank you for getting back to me.
I’ll send a screenshot of what I currently have. The data shows that Sean had 3 instances of Tech Issue yesterday…however, he actually only had 1 but it was for a duration of 42 minutes meaning it was within 3 within 15 minute intervals.
Ideally, I would like this to only show as 1 instance with the duration time showing as 42 minutes.
Please let me know if you need any further information.
Hi @LParkes,
Thanks for sharing the additional information! What is the calculated field look like for ‘Activity type count’..I assume that there are some other fields in play that are not allowing you to run a simple sum calculation, aggregated by person and date?
1 Like
Hi Brett,
Apologies in the delayed reply. The calculated field for Activity type count is as below:
countIf({Activity Type}, {Activity Type} = “Tech Issue”)
Not sure if I need to be adding more into this calculation in order for me to get what I need?
Hi @LParkes,
In terms of an instance like this, I would suggest looking in to using the sumOver function to accomplish. You can sum the scheduled time by a number of partitions; in this case you would want to include person name, and activity type. Then you can just apply filters for date range and activity type to equal ‘Tech Issue’. This should provide you with an overall total of the time as opposed to separate 15 minute intervals.
Let me know if this could work for your scenario or if you have any additional questions
1 Like