Hello,
I am trying to create a distribution that shows the count of transactions ("jobs") grouped into buckets of hours. I thought using a window function to partition the hours buckets by Job (X-axis) and then doing a count(distinct) of jobs as the Y-axis would work, but it's not grouping correctly. When I set the Hours Buckets as just the total sum of hours without the partition, it puts all data into the largest bar. Which makes sense, since the total hours across all the data will be in the largest bucket. I tried adding a partition with the unique transaction to the buckets formula, but it doesn't aggregate correctly and shows the same bucket labels repeated many times.
case
when sum(Total_Hours_Worked
) over(partition by DIV_Order_Num
) < 50 then '0-50'
when sum(Total_Hours_Worked
) over(partition by DIV_Order_Num
) < 100 then '50-100'
when sum(Total_Hours_Worked
) over(partition by DIV_Order_Num
) < 150 then '100-150'
when sum(Total_Hours_Worked
) over(partition by DIV_Order_Num
) < 200 then '150-200'
when sum(Total_Hours_Worked
) over(partition by DIV_Order_Num
) < 250 then '200-250'
when sum(Total_Hours_Worked
) over(partition by DIV_Order_Num
) < 300 then '250-300'
else '>300' end