I have a huge dataset of 12 gb which has different values for an order number. I want to count all distinct order numbers and take average of them. And display Kpis like average orders for the selected date range.
When I try to do that with distinct , I cannot take an average of those measures. How can I convert those order numbers into distinct values(for the entire dataset) and then take an average of that.
Hi @Prajakta,
Can you clarify “average orders”? Is it average orders per day, per customer, per product?
Hi @David_Wong
Like here, I can take the average amount for all orders (based on the entire dataset) because total amount is a measure here an not a dimension.
This is how it is present in the dataset.
I want to be able to do the same for order number as well.
Here, I can just do count and count_distinct and not average. Is there a way in which I could perform average here?
Your first screenshot shows that ro_number is a string.
If you want to perform calculations like average on it, you have to convert it to integer or decimal in your dataset.
Hi David,
This is now I’m calculating Ros closed. So for the entire dataset, it is unique order numbers.
Ros closed is just distinct count of ro numbers so in this visual, it is essentially the same.
In this image, for total amount, I can take the sum in the first column and average in the next. So how can I do the same for Ro_number and Ros Closed?
Or can you give me a way to calculate average ros closed month over month? For the selected date range?
What result are you expecting for average ros closed in Nov 2022? Are you expecting 24/30 because there are 30 days in November?
Yes, and in December it should be 3014/31 and so on
First you have to calculate the number of days in a month:
ro_close_month = truncDate(‘MM’, {ro_close_date})
days_in_month = dateDiff({ro_close_month}, addDateTime(1, ‘MM’, {ro_close_month}), ‘DD’)
Then divide your distinct count of order numbers by the number of days in a month:
average_daily_orders = distinct_count({ro_number}) / min({days_in_month})
This worked perfectly, thanks David !
