topic Re: FIRSTSORTEDVALUE in App Development

FIRSTSORTEDVALUE

Thu, 03 Mar 2016 20:06:55 GMT

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.

Re: FIRSTSORTEDVALUE

brunobertels — Fri, 04 Mar 2016 08:12:23 GMT

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

Re: FIRSTSORTEDVALUE

Fri, 04 Mar 2016 08:49:21 GMT

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.