Hi everyone, for O2C we want to be able to analyze the top 10 customers (based on net order value) vs the other customers. Is there a dynamic way to define/filter the top 10 customers based on the net order value?

Hi Dayenne,

This should be possible with a couple of steps.

• Use the function INDEX_ORDER to sort the customers based on net order value. In the snippet below, the customer ID is returned if the index (based on the net value column, could also include a SUM() ) is 10 or lower. You could also put this piece of code in a 'Static Value' variable. The result is than calculated when opening the analysis.

CASE

WHEN INDEX_ORDER ( <table.customer_ID_column> , ORDER BY ( <table.net_order_value_column> ) ) <= 10

  THEN <table.customer_ID_column>

  ELSE NULL

END

• Then, create a statement, that tests if the customer ID matches with one of the ID's selected above. Here I used 'Yes' and 'No' as result, but of course you could replace this for every calculation you wish, or use it in a component filter.

CASE

WHEN<table.customer_ID_column> IN ( < formula or variable above > )

THEN 'YES'

  ELSE 'NO'

END

I created a example below, where I order on PO-numbers from lowest to highest. Here you can see that the 10 lowest PO-numbers (there are duplicates) have a different outcome than the others.

I hope this helps!

Kind regards,

Jan-peter

Hi Jan-peter,

thank you, this is helpful!

Regards, Dayenne

Hi Jan-Peter, the SUM() Statement in Order by doesnt work for me, do you have a solution?

Many Greetings

Lukas

Hi Lukas,

What is the error message?

Kind regards,

Jan-peter

Hi Jan-Pater,

thanks for you reply.

There is no aggregation function allowed in INDEX_ORDER.

Many Greetings

Lukas

Hmm, it could be that I didn't tested that. If you work with a knowledge model, you might be able to create a workaround for this by adding an attribute to the corresponding record, having a PU_SUM to the order amount.

Not sure if this will work though.