Hi,

We plot shops in a graph based on Average Spending per client and CandyConversion. I have attached an example table.
The table contains data on shop-week combination. However, the number of records per shop per week differ. Shop 13, can have 60 records in week 1 and 80 records in week 2. The Spending value for a week is in all records the same.

Our difficulty starts at plotting the shops (we need a marker per shop) based on average spendings. When the file has data of 12 weeks, you will find 12 unique spending values per shop. The actual average is summing these 12 values and divide by 12. However, because Omniscope takes the number of records into account (which differ per week), the average is different.

A solution would be to aggregate on Shop id and Week, but that results in a marker per shop-week combination and we need a marker per shop.

How can we get a marker per shop, whereby the average per shop is calculated as the sum of all unique values found divided by the number of unique values.

Hope this makes sense and that you can help us

Thanks
Arjan