Trying to create vertical bar chart that shows Count of Jobs based on Hours of each Job?

ccccc4

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

Screenshot 2024-03-08 151722.png

GrantSmith

@ccccc4

It's having multiples of the same buckets because likely you're using another field in your sort or chart that doesn't appear in your FIXED function. Also you'll need to aggregate yout aggregate as @marcel_luthi points out.

If you had some anonymized data for us to work with, that would be helpful.

marcel_luthi

This sounds like a good option to use a Histogram (if you don't need that much granular control over the partitions). If you need custom partitions, I'd strongly advise doing a View of your dataset that already aggregates the total time at the DIV_OrderNum level and then graph based on this with a simple ETL to bucket them. If you can't or really don't want to for some reason, I'd use FIXED instead of OVER, something like:

CASE
WHEN SUM(SUM(`Total_Hours_Worked`) FIXED(BY `DIV_Order_Num`)) <50 THEN 'a)0-50'
WHEN SUM(SUM(`Total_Hours_Worked`) FIXED(BY `DIV_Order_Num`)) <50 THEN 'b)50-100'
WHEN SUM(SUM(`Total_Hours_Worked`) FIXED(BY `DIV_Order_Num`)) <50 THEN 'c)100-150'
WHEN SUM(SUM(`Total_Hours_Worked`) FIXED(BY `DIV_Order_Num`)) <50 THEN 'd)150-200'
WHEN SUM(SUM(`Total_Hours_Worked`) FIXED(BY `DIV_Order_Num`)) <50 THEN 'e)200-250'
WHEN SUM(SUM(`Total_Hours_Worked`) FIXED(BY `DIV_Order_Num`)) <50 THEN 'f)250-300'
ELSE 'g)>300' END

Then make sure your card is set to sorted by this new field only and that the what will show is a

COUNT(DISTINCT `DIV_Order_Num`)

Again I'd preferred to go with a GroupBy view of the table that aggregates things at the DIV_Order_Num instead, to make calculations simpler for anyone that might take a look at this later.

ccccc4

Thanks Marcel!

If I could use a dataflow or view to pre-aggregate by Div_Order_Num, I think this card would be very easy to build. Unfortunately, the person who requested it wants the ability to filter the data by individual labor activity types within each Order (Mechanical, Engineering, Painting, etc.)

This means that the card needs to be build on the granular data instead of total hours by Order_Num.

I tried using FIXED(BY instead of OVER (PARTITION BY) but it yielded the same result. Instead of aggregating every Order_Num that falls into each bucket into a stacked bar, it creates a separate bar for each Order_Num. In this example, you can see that Order #'s 184282 and 185161 both have hours in the 0-50 bucket, but instead of one bar with a count of 2 it creates 2 bars with counts of 1 each.

ArborRose

CASE
WHEN Total_Hours_Worked >= 0 AND Total_Hours_Worked < 50 THEN '0-50'
WHEN Total_Hours_Worked >= 50 AND Total_Hours_Worked < 100 THEN '50-100'
WHEN Total_Hours_Worked >= 100 AND Total_Hours_Worked < 150 THEN '100-150'
WHEN Total_Hours_Worked >= 150 AND Total_Hours_Worked < 200 THEN '150-200'
WHEN Total_Hours_Worked >= 200 AND Total_Hours_Worked < 250 THEN '200-250'
WHEN Total_Hours_Worked >= 250 AND Total_Hours_Worked < 300 THEN '250-300'
ELSE '>300'
END

trafalger

100% agree with @marcel_luthi, this would be a great thing to add to a dataflow. Do a group by by Job, add it back to the job as a "Job Total" hours and then you could even add in the classification to the job level as well. Well worth it to spend 30 seconds in a dataflow vs. trying to create a crazy beast mode.

GrantSmith

@ccccc4

It's having multiples of the same buckets because likely you're using another field in your sort or chart that doesn't appear in your FIXED function. Also you'll need to aggregate yout aggregate as @marcel_luthi points out.

If you had some anonymized data for us to work with, that would be helpful.