Here, I have a few deliveries which are marked as ‘late’. There are a total of 13 late deliveries in a small period of time (1 week). I’m trying to build a histogram to show the distribution of lateness of these deliveries.
The issue comes with how data is organised, which causes it to get duplicated when joining tables, forcing me to use the LAC function. Here are the formulas I’m using: dateDiff(deliveryDate, deliveredAt, "MI")which gives me the ‘lateness’ min(Lateness, [orderID]) is the LAC function and minOver(Lateness, [id], PRE_AGG) is the LAC-W
The table in the image above shows the correct number of late deliveries, however the histogram does not. What can I do differently? I’ve attemped to use countOver as well, which does not give me the right results.
Hello @rohit_SB, what it is displaying as the aggregation in your field well within the Histogram? Maybe it is returning the value as a sum, and if you are using LAC-W aggregations, a single value may be returned on multiple rows. You will want to make sure you are aggregating the field well by something like min or max so each distinct value is only account for once.
If you still are receiving incorrect values, I would be curious to see if the data displays more appropriately in a bar chart. Sometimes the histograms can be a little fickle, so if you still have trouble after attempting my first suggestion, maybe try changing the visual and see if you get closer to your expected output.
There is no way to change the aggregation at visual level for histograms, since it uses the value field. However, based on your suggestion, I’ve already implemented the minimum value: min(Lateness, [OrderID]). Would that not be the right way to go? Doing it this way: min(min(Lateness), [OrderID]) doesn’t make much sense to me either. When using a window function, I’m also sticking to getting the minOver value and grouping it by OrderID.
The weird part is that it displays the information accurately on my table, but doesn’t work the way I expect it to on the histogram.
I did try the bar graph as well, but I’m seeing the same issue on it as well
Hello @rohit_SB, oh I see. I should’ve checked the visual first, I see that you are unable to aggregate in the field well. Have you tried applying the minOver function instead? This is a bit difficult for me to test on my end because the data is very specific. It may also be worth setting up a demo of this in QuickSight Arena where I could assist with the calculations as well.
Hey @DylanM , I’ve created a staging dataset and also put it up on Arena.
Here’s the link Staging Dashboard
What you’ll notice is that there’s a Client ID, an Order ID and an Order Items ID. This is post-join, and this tends to affect the way the histogram works. If you remove the Order ID and Order Items ID, it’ll still show up on the histogram I think
Hello @rohit_SB, I have spent a little time working on the analysis in Arena and haven’t quite figured this out yet. Am I correct in remembering that your data is static as the dataset and you are not running any queries? I am wondering if this would work as expected if you were able to create a dataset at the Order ID level, with single rows per order, containing the min value of lateness for that order. I haven’t been able to find a way to remove the aggregation caused by the many order items IDs within each order ID. I can look at it a bit more and see if I can find an alternative.
Yup, pretty much. I can’t run any queries because I don’t really have access/control over the data hosting and storage (DocDB/S3). Your suggestion would be the only way through since I’ve tried a bunch of functions and techniques (lac aggregate/window, ifelse structures, etc). I’d love to be able to query the min lateness grouped by ID at order ID level but this is a struggle for now
Thank you for helping out!
Is this what you’re looking for? Take a look at the “LAC-W as Dimension” sheet. I’m not sure I understood what you’re trying to do. Are you trying to count how many orders have each lateness value? LAC-W as Dimension
Hello @rohit_SB, I know the visual ends up looking a little different than what you have currently, but from what I can tell, the solution suggested by @David_Wong works. Let me know if you have any remaining questions, but I can mark his response as the solution.
Hey guys @David_Wong@DylanM , the bar chart does work. While this isn’t the best way to go forward, it’s the best solution with regards to time. Thanks a lot for your help guys.
