Aggregating Data Using Multiple Columns

0
Hi Everyone,   I have an entity that stores sales data, including the customer (by association) and the sales value. In a relatively basic dashboard I'm trying to present to a salesperson I'd like to be able to show the top customer for that salesperson by sales value.    To do this I'd need to determine the total value of sales for each customer and then get the top one in the list. I assume I'll need to aggregate the data somehow, but how would I do this by total value and then by client - how would I then sort the values in top down order so I can get the top one?   To extend this a bit, I'd like to be able to show the sales person their top 10 customers if they click on the top client widget in the dashboard.   I've looked at creating a non-persistable entity to store the total values by customer once I've determined what these values are, but I'm a bit stuck on how to approach this. A lot of the community answers I have read talk about using OQL for this, but is there a simpler approach?   Thanks!
asked
2 answers
0

Hi David,

You can determine the top customer by sales value using a combination of microflows and database retrievals without needing OQL.

First, retrieve all sales records associated with the logged-in salesperson. Then, create a non-persistable entity (e.g., CustomerSalesSummary) with attributes for the customer and total sales value. In a microflow, loop through the sales records, sum up the total sales per customer, and store the results in this entity. Once you have the aggregated data, sort the list in descending order based on total sales value. The first entry in this sorted list will be the top customer.

If you also want to show the top 10 customers when the salesperson clicks on the top client widget, simply retrieve the first 10 items from the sorted list and display them in a list view or data grid.

This approach keeps things simple and avoids the complexity of OQL while still being efficient. However, if you’re working with a large dataset and need better performance, you can consider using XPath or OQL queries.

Let me know if you need any further clarification!

Hope it helps!

answered
0

Hi David,

 

I think you can achieve this in Mendix by avoiding OQL unless you have a very compelling reason to use OQL, the Aggregate activity within a microflow is the best solution. You can simply retrieve sales, aggregate by customer (summing sales value), create a non-persistable entity for totals, and populate it in a loop then on click, a microflow retrieves/aggregates again, sorts, and opens a page with a data grid (page size 10) for the top 10 customers.

 

Hope it will help you.

answered