Hey all, looking for ideas.
I have a data set that has shipping lanes (Origin/Destination) and then a summed cost and revenue for each shipment on each lane. Then a margin based on that for each lane and shipment.
I have each lane summarized into a monthly average, but I would like to be able to break that down a little further to show the groupings of costs, groupings of revenue and grouping of margin. That way we can see how many shipments fall into each group, what the cost and margins are for each group and then also show how many shipments are outliers and didn't fall into a group.
Example would be something like:
CA to MN - 130 shipments cost $300- 400, Margin $20-30, Revenue $320-430.
CA to MN - 20 shipments Cost 400-500, margin $5-10, Revenue -$10 to -15
A couple questions/difficulties:
- I would like to not use predefined ranges for costs, margin and revenue. I'd rather the system broke them up into groups with manual parameters such as number of ranges and manually setting the range for a given group (ie $100 difference in costs lumped together).
- Repeated three times based on cost, revenue and margin.
- Would there be a way to use the largest group as the 'mean' and measure the other groups from the largest group? Or to group things statistically based on the largest group?
- Assuming this is better to do at the ETl level than a card?
- Need to keep the individual shipment level detail so we can look at individual shipments within each grouping.
Maybe not a single answer here, but interested in hearing how others might approach it. Also, my programming is pretty much 0, but able to write cases' etc. decently in the ETLs. Do have access internally to programmers that could help if need be.
Thanks in advance!
