2 Replies Latest reply: Mar 4, 2016 3:49 AM by Dimitris Raoulis RSS

    FIRSTSORTEDVALUE

    Dimitris Raoulis

      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

        • Re: FIRSTSORTEDVALUE
          bruno bertels

          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
              Dimitris Raoulis

              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.