My customer asked for a measure of the frequency of orders. It means he wants to calculate the average of the time between orders.
So I created a calculated field called “prev Order” that uses the “lead” function and brings the date of the previous order, so I could calculate with the “datediff” function the time between the current order date and the previous order date. Then I want to calculate the frequency of orders creation = average of the time intervals, so I want to create a calculated field that does averages on all the datediffs. That is impossible and gives error alerts:
Hello @Noys, I think we can work through this to get your desired solution. We should try breaking this function up a little to assist with debugging.
Create a leadTimestamp field first to add it to every row: lead({nth_malfunction_start_timestamp}, 1)
Then create a timestampDiff field to grab the dateDiff between your current and lead timestamp: dateDiff({nth_malfunction_start_timestamp}, {leadTimestamp}, 'DD')
Now try adding the timestampDiff field to your visual and in the field well aggregate as an average. Let me know if that works or if another error occurs in that process!
Hey @DylanM Thank you so much for your detailed response!
Maybe I didn’t explained myself properly. actually it’s exactly what I tried to do.
The problem it’s that I can’t do average on the “timestambDiff” field, because the error I attached above.
If there was an option to do this avg on this field it was perfect. but avg on this field is impossible.
please let me know if you have any idea to solve it.
Thanks again!
Noy
Hello @Noys, would the option I mentioned not even allow you to set the aggregation as average in the field well of the visual? I’d be curious to see a screen shot of that if possible to see what is happening.
Otherwise, I would suggest running those calculations in your SQL when you ingest data into the dataset, then there won’t be any aggregation issues within QuickSight.
Hello @Noys, when I say try to aggregate as an average in the field well, I mean to add your dateDiff calculated field to the visual and select average like how it is shown in the image below. I am curious if it will provide you with options there rather than using a calculated field to average the result.
Hello @Noys, if that is the case, I think the issue might be coming from either the Lead function or the Max() function within your dateDiff calculation that is not allowing QuickSight to aggregate the value any further. I believe the best solution will be to add the calculated fields for Lead and dateDiff to your SQL query on your dataset, that will ensure you are able to aggregate the average within QuickSight.
Thanks a lot for your help. It is a nice idea. Actually I prevent adding fields to the data set because it’s the customer’s data set, which should be used by other analysis and users, anyway I think I got no other choices, so probably I’ll do it.
