AvgOver - trying to create a benchmark line for purchaseprice over time

Q&A
, , , , ,
1

Hey all,

i need some help figuring out what’s going wrong. I have a nice little dataset where we have data for multiple shops. This particular dashboard is intended for only a certain number of these shops, so there is a filter on the ShopID to only show those shops.

I have a very basic Clustered bar combo chart where i plot the average purchaseprice on the Y-Axis and the Purchasedate for the last 12 months on the X-axis aggregated on months.

On the line-part of the visual i would like to show a ‘benchmark’ line where it gives the average of ALL the shops excluding the shopID filter. I’ve tried making a AvgOver function called AvgOverPrice:

avgOver(PurchasePrice,[PurchaseDate],PRE_FILTER)

It calculates without error, however the results it gives are faulty.

for instance if you were to look at the numbers for Jun 2024, it correctly calculates the normal average purchaseprice of €288,53. That number is correct when compared to the raw data. The line however tells me the Average should be €282,49, but when i compare that to the raw data it should actually be €299,89

image
image1309×541 51.1 KB

I don’t really understand what’s going wrong and hope someone can help!

Appreciate all your help,
Jorit

2

Hi @Jorit and welcome to the Quick Sight community!

What if you tried partitioning by Shop ID instead of purchase date, let me know if this produces a different outcome for you:
avgOver(PurchasePrice, [ShopID], PRE_FILTER)

1 Like
3

Hi @Brett thanks for you help,
when i make that change it does give a different output, however it goes even further away from the right amount :stuck_out_tongue:

It’s now showing €237.86 instead of 299.89

image
image1095×452 37.5 KB

if you have any further ideas please let me know, appreciate it!
Jorit

4

Hi @Jorit,

When you use avgOver with PRE_FILTER, you’re telling Quick Sight to calculate the average while ignoring your filters. For example, if you have 5 shops and you select 3 in your filter, the avgOver is calculated based on all 5 shops. Are you trying to calculate an average based on the remaining 2 shops?

the average of ALL the shops excluding the shopID filter

3 Likes
5

Hi @David_Wong
I’m trying to get the average of all the shops, so all the 5. However it keeps giving me different values than it should according to the dataset…
It should be working, but it isn’t which is really weird… i wonder what’s going wrong.
If you have any idea or any information you might need to help please let me know!

1 Like
6

Hi @Jorit,

What’s the granularity of PurchaseDate? If the granularity is day, can you try this?

avgOver(PurchasePrice, [truncDate('MM', PurchaseDate)], PRE_FILTER)
1 Like
7

Hi @Jorit,
It’s been awhile since we last heard from you, checking in to see if you had any additional questions?

If we do not hear back from you within the next 3 business days, I’ll go ahead and close out this topic.

Thank you!

8

Hi @Jorit,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for any relevant information that may be needed.

Thank you!