Hi everyone. I’m trying to calculate the Average Order Value in my analysis, and using that value to display on a horizontal bar graph to display the AOV based on time. Here’s my logic:
AOV = GMV/# of Orders
GMV = min(GMV, [id]) (I’m doing this to ensure that no values are being duplicated due to the way our datasets are joined).
Number of Orders = distinct_count(id)
Therefore, AOV = sum(GMV)/#ofOrders
This works when adding to a table, as seen below:
However, I can’t do the same when trying to visualise it:
Hello @rohit_SB, part of the problem may be that you are using the min of GMV by ID in the calculation, that may be throwing it off. Is the format of the bar graph you are trying to create going to have the date field as your x-axis and the AOV as your y-axis? Is the bar graph going to display the total AOV of multiple IDs for each date period or the value of a single ID over time?
As long as the aggregated field is in the y-axis, you should be able to make something like this work: AOV = minOver({GMV}, [{id}], PRE_AGG)/distinctCountOver({id}, [], PRE_AGG)
Then, if you wanted the value for more than a single ID in one of the bars, you could try something like this: TotalAOV = sumOver({AOV}, [{dateMonth}], PRE_AGG)
I used dateMonth as an example, but week format, day format, etc. should work fine as well. Let me know if this helps or if this isn’t quite what you were looking for.
My bar graph will have date in the x axis, aggregated by month and the y-axis will consist of the the AOV. This will consist of the total AOV for multiple IDs for the specified date period. I could also display by each individual ID, but it doesn’t make much sense to do that as there’s over a 1000 ID’s.
I’ll try the first option out using LAC-W and will let you know asap. Thank you @DylanM!
Hey @DylanM , tried your suggestion out
This is for a specific period of 2 days, where a total GMV of 1799 was seen. Using the LAC-W formula, I’m getting a blown up value. GMV (USD) (LAC-W) = minOver({GMV (USD)}, [id], PRE_AGG)
The total number of orders placed in this period was 17, and using the distinctCountOver({id}, [], PRE_AGG) gives me a larger number too.
Using the AOV, and then total AOV formula, I’m getting the wrong values. The AOV I need is 105.86 for example. To put it simply, all I need is a bar chart that has date on the x-axis and AOV on the y-axis, where the date is aggr. by month and the AOV is in USD. It should be an average across all clients, which is why I’m taking the overall GMV and dividing it by the number of orders.
Am i missing something?
Edit:
These are the aggregations I’ve applied at visual level for the steps you mentioned.
Hello @rohit_SB, I am wondering if we need to add the dateMonth field to the partitions as well for the first AOV function. Does minOver({GMV (USD)}, [{id}, {dateMonth}], PRE_AGG) get you the value you are expecting for GMV? If so, I think we will want to add it to the denominator as well. The AOV function would look more like this: AOV = minOver({GMV (USD)}, [{id}, {dateMonth}], PRE_AGG)/distinctCountOver({id}, [{dateMonth}], PRE_AGG)
If I am correct, that function should give you a GMV for each Corp ID divided by the number of Corp IDs active that month. Check both the numerator and denominator on their own to see if those numbers are returning correctly. If that looks good, then we can try adding it back into the TotalAOV function which will sum the AOVs for each of the Corp IDs active that month. Let me know if that helps!
Hello @rohit_SB, is there anything else I can assist you with on this topic or did my last reply help guide you to your desired output? Feel free to mark my last response as the solution if it worked, or let me know if you have any remaining questions on this issue. Thank you!
Hi Dylan, I just attempted this today. Sadly, this doesn’t work either
I followed through and created the LAC-W formula exactly as you described, but the values are significantly off
Hello @rohit_SB, are they showing incorrectly at the Corp ID level, the totals, or both? When you partitioned the LAC-W functions by the Month, did that improve the returned values at all?
Hello @rohit_SB, I will archive this topic since we have not heard back from you. If you still want assistance with this issue, or need help resolving something related to it, please post a new topic in the community and feel free to link this topic to provide relevant information. That will ensure you are at the top of the priority list to receive a response from one of our QuickSight experts. Thank you!
