Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Alternative for a Loop in Chart Expressions

Hello,

I need to use a loop to define the colours of my bars in a chart.

I'm creating a bar chart that shows the number of goals of each football team, but my core is dynamic and I don't know how many teams I will receive (maybe 100...). What I'am thinking is create a expression in "Background Colour" from Expressions tab like this:

While (var x <= "Number of Teams)
{
     RGB(X + 10, Y + 10, Z + 10)
}

I know that is not possible. Anyone have any better idea to do this?

Thanks!!

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

In the load script you could create a dimension table for your teams and set a variable to contain the Count() of your distinct Teams.

Then Left Join onto Team table from resident Team table and create you RGB using RecNo() and the Count value in your variable.

When you have the rgb for each Team in your Team Table set your "Background Colour" to the team's rgb

View solution in original post

9 Replies
Anonymous
Not applicable
Author

In the load script you could create a dimension table for your teams and set a variable to contain the Count() of your distinct Teams.

Then Left Join onto Team table from resident Team table and create you RGB using RecNo() and the Count value in your variable.

When you have the rgb for each Team in your Team Table set your "Background Colour" to the team's rgb

Not applicable
Author

Many thanks Bill!

Can I have a variable like this:

Let X = Count(distinct Team)

Then I put this into a for loop with a increment process.

maxgro
MVP
MVP

you can add a teamid (build with autonumber or recno or etc.....) to your script and use the colormix1 function (in background color expression, file , colormix wizard) to define the color

1.png

MarcoWedel

maybe also possible as background color expression:

ColorMix1(RowNo()/NoOfRows(),LightBlue(),LightRed())

regards

Marco

Not applicable
Author

Thanks Marco,

It's a nice option

Not applicable
Author

Bill can you post a example of the code (specially the part of the left join)?? Thanks

Anonymous
Not applicable
Author

See attached sample qvw and here is the script :

Teams :

LOAD * INLINE [

    Team, Points

    Excellent Team, 10

    Good Team, 8

    OK Team, 5

    Iffy Team, 3

    Could Try Harder, 1

];

CountTeams:

Load Count(distinct Team) as CountTeams

resident Teams

;

Let vCountTeams = Peek('CountTeams')  ; 

Drop table CountTeams;

Trace vCountTeams:  $(vCountTeams) ;

Left Join ( Teams )

Load

  ColorMix2(( ( (RecNo() -1) /  ( $(vCountTeams)  -1 )  ) * 2 ) -1 ,Green(),Red(),Yellow()) as TeamColorMix,

  *

resident Teams

;

Not applicable
Author

Just Perfect Bill Many thanks!!!

Anonymous
Not applicable
Author

If you wanted to you could also in the script order the teams by the sum of their Points to rank them in order by how they are doing and put that into the ColorMix2() so the leading teams are at the green end and the training teams at the red end.

One advantage of setting the colour in the script is that the colours are then fixed, but if done in the front end colours may change if selections mean not all the Teams are selected.  Also as a matter of principle I always try to put all calculations in the script when viable to do so.