I have transactional data, and I want to plot number of visitors by department. So I have DATE, DEPT and USER. I want count users by department and then visualize this using a bar chat.
The query I'd use for binding could be:
SELECT DEPT, COUNT(USER) AS VISITORS
WHERE DATE BETWEEN 'START' AND 'END'
GROUP BY DEPT
I want the ability to plot these bar charts (or pie charts) for any time frame the user decides. So if I have a data set for a year, I want to be able to plot a bar chat for any selected dates.
I can do the bar chat without a time element, but I'm struggling with how to implement the aggregation when there is a time element involved. I want my query to recalculate the counts when a new date range is selected.
Any ideas of how to implement this with Perspective Components?