So I have an requirement where I want to project the count of customers in a Vertical Bar Chart whose average delivery time is greater than equal to 120 and less than 120 separately individually. Hence for which I have created this calculation
Customers >= 120 :
Ifelse(avg({Average Delivery Time},[{Customer},extracted(“MM”,created)]) >= 120, 1, 0)
With having create_date(Month) in the X Axis and Customers >= 120(SUM) in the Values i’m getting the accurate results which I have validated.
However lets say I drill it down this to weekly in the Vertical bar chart and no.s are not adding up correctly. Hence can you let me know whats wrong here.
And what would be the alternative in this case? And also while I was not using the month truncdate earlier in the calculation it was not showing me the accurate results on monthly basis hence due to which I had to add truncdate.
Hi @samytar97
Can you try trunc date function with WK & MM for week level & month level calculation .
avg(Sales,[Product,truncDate(‘WK’,{Order Date})])
avg(Sales,[Product,truncDate(‘MM’,{Order Date})])
If its not working , could you please upload the sample dataset and create the sample visual Arena and share it with us to check further.
Thanks
Vinod
1 Like
Hello @apjvinod
So in the dashboard I have two visuals one is the bar chart and below is the table column to validate the data. As I mentioned my requirement is to project the bar chart where I can showcase the count of customers in a Vertical Bar Chart whose avg price is greater than or equal to 200 and less than 200 in two separate bars however lets say when you drill down to weekly its not matching the below tables no.
Lets take Dec 17, 2024 as an example so in the bar chart its showing as there are 11 Customers whose Average Customer Sales >= 200 however if we validate in the table it is 12. Customers
And similarly for Customer Sales < 200 in the bar chart its showing as 23 Customers whose Average Customer Sales >= 200 however if we validate in the table it is 22. Customers
So what should I do here to ensure for each time frame in the bar chart all the metrics should correspond equally to the table?
[Arena link](Average of Customer Sales Price)
1 Like
Hi @samytar97
Interesting case. You are calculating the Customers Avg sales at the month level. When you drill down to weeks the data is still filtered for the date range you have but displayed grouped by week. Here you got the count of 11
However, if you filter the date for the week of Dec 17 which is what I think you are doing for validating the data then you get 12.
I looked at this by exporting the data to excel based on the above behavior and found something interesting.
Count of 11 when I changed the Vertical Chart to Table when showing by week
Count of 12 when I filtered the data for the week of Dec 17
So there is no right or wrong here; but how you are computing the avg and the way you are filtering that creates the confusion.
Regards,
Giri
Hello @Giridhar.Prabhu
The reason for adding extract filter for month level in the calculation as previously without it was giving me similar incorrect results even at the month level hence an month level truncdate was added which indeed provided the accurate no.
And yes you are correct when we filter the data exactly by the week of Dec 17 it is giving me accurate results. However let just say I want to see the trend of 5 weeks like from Nov 26,2023 to Dec 24, 2023 then in that case the no.s are not matching the data from the table.
Let just say you have an similar requirement to showcase the count of customers whose avg price is greater than or equal to 200 or less than 200 what will be your approach in calculating the formula ?
Thanks
1 Like
Hi @samytar97
Since the level of aggregation is changing dynamically (Month, Week, Day etc) you could possibly use the ${visualDimensions} parameter in your calculated field.
With this calculation your explained use case seemed to work fine as I got 12 when I drilled down to week for Dec 17. You could test further and accept it as solution if results work as expected.
You can refer the following for further reading.
Regards,
Giri
2 Likes
Hello @Giridhar.Prabhu
Thanks for providing the solution this works as expected. Visual Dimensions is something I have learned new today.
1 Like