Filtering on 'Top 10' when using two metrics
I'm looking to create a graph which dynamically shows the last 4 weeks sales trend by customer, filtered on the top 10 customers (by sales revenue). However, when I add 'week' and 'customer', it filters on the top 10 rows (i.e sales revenue by customer per week) and not specifically the top 10 customers. Is there a way to create this?
I could filter on 10 customer names, but as this will likely change each week, it will not be dynamic.
Thank you!
-
Hi Sam Greenwood
What version are you on? This looks like a perfect application of our inline subquery feature, which came out in 5.2. With inline subquery you can do this (using quotes just to delimit the fields, don't use in search bar)
sales 'sales group' week 'last 4 weeks' 'sales group' in ('sales group' sales 'top 10')
This does a query inside a query. The "inside" query:
'sales group' in ('sales group' sales 'top 10')
should get you the top 10 sales groups, putting the result in 'sales group'. Then the outside query:
sales 'sales group' week 'last 4 weeks'
should give you the sales of those top 10 sales groups.
Margie
-
Margie Roginski
Thanks very much for the quick response Margie, unfortunately we are still using 5.1 and I am unsure if we will be upgrading. Is this not currently possible in 5.1?
-
Hi Sam,
In 5.1, you could create a view of the data and then use that as a data source to do your top 10 filter
-
Thanks Marc. I am not too familiar with views but know how to create one. If I create a view filtering on the top 10 customers, will this dynamically update if the top 10 change? (I am assuming this is the easiest way to approach this?)
-
Sam Greenwood off the top of my head without testing it I think it will update.
You could also have a materialized view and set a schedule to update it
-
Sam Greenwood Yes, it should dynamically update based on the top 10. Inline subqueries were created to avoid having to create a view, but views are definitely another way to get the same functionality. Marc Price thanks for your response!
