Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

FIRSTSORTEDVALUE

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.1.png

Tags (2)
1 Solution

Accepted Solutions
Not applicable

Re: FIRSTSORTEDVALUE

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.

2 Replies
brunobertels
Valued Contributor

Re: FIRSTSORTEDVALUE

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

Not applicable

Re: FIRSTSORTEDVALUE

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.