FIRSTSORTEDVALUE

[#1]

Hi Community as you can see in the attached image i have applied the below function in a text box showing the right data, retrieving the Customer Code based on top Customers (Sales):

FIRSTSORTEDVALUE(cust_code,-aggr(Sum({$<Year= , Item=, ProjectStatus={3} >} AGGR({$<Year= , Item=,  ProjectStatus={3} >}Sum(Revenue_det),Optsn)),Customer),1)

Required result : 0323

But the problem is when i am trying to color the bar chart respectively for my top 5 :

1st ---> blue()

2 ----->red ()

...

5  .....and so on

I have used if statement using the above function and color.

if ( cust_code = above_function, blue())  and it does not affect my bar chart .

Any experience by coloring Top 5?

If i can color my top 5 with unique colors  for every Customer i want to place this color expression in a scatter plot to color projects of this Customer respectively.

[brunobertels]

Hi Dimitris

May be have a look in this thread you may found color approach that could help you

https://community.qlik.com/thread/130571

first approach with a valuelist function , and second approach with creating a table with a list of your project and a RGB value.

regards

Bruno

[#3 · Accepted solution]

Hi Bruno, if i create the synthetic dimension with the help of valuelist it affects to my top 5 customers bar chart as i have now.

I managed to color the top 5 barchart using:

if (  Customer = concat (IF( aggr ( Rank(Sum({$<Year= , Item=, ProjectStatus={3} >} AGGR({$<Year= , Item=,  ProjectStatus={3} >}Sum(Revenue_det),Optsn))),Customer)=1, Customer)),LightBlue(), if ( Customer = concat (IF( aggr ( Rank(Sum({$<Year= , Item=, ProjectStatus={3} >} AGGR({$<Year= , Item=,  ProjectStatus={3} >}Sum(Revenue_det),Optsn))),Customer)=2, Customer)), blue(),if ( Customer = concat (IF( aggr ( Rank(Sum({$<Year= , Item=, ProjectStatus={3} >} AGGR({$<Year= , Item=,  ProjectStatus={3} >}Sum(Revenue_det),Optsn))),Customer)=3, Customer)), red(),if ( Customer = concat (IF( aggr ( Rank(Sum({$<Year= , Item=, ProjectStatus={3} >} AGGR({$<Year= , Item=,  ProjectStatus={3} >}Sum(Revenue_det),Optsn))),Customer)=4, Customer)), yellow(), if ( Customer = concat (IF( aggr ( Rank(Sum({$<Year= , Item=, ProjectStatus={3} >} AGGR({$<Year= , Item=,  ProjectStatus={3} >}Sum(Revenue_det),Optsn))),Customer)=5, Customer)),green()) ) )))

using the  function  rank(sum).

I have now the challenge to color in a scatterplot the projects for each one Customer respectively.

Thank you for your help.