topic Re: Alternative for a Loop in Chart Expressions in QlikView

Alternative for a Loop in Chart Expressions

Wed, 25 Nov 2020 16:16:04 GMT

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!!

Re: Alternative for a Loop in Chart Expressions

Anonymous — Mon, 14 Mar 2016 19:34:47 GMT

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

Re: Alternative for a Loop in Chart Expressions

Mon, 14 Mar 2016 21:35:10 GMT

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.

Re: Alternative for a Loop in Chart Expressions

maxgro — Mon, 14 Mar 2016 21:51:42 GMT

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

Re: Alternative for a Loop in Chart Expressions

MarcoWedel — Mon, 14 Mar 2016 23:00:07 GMT

maybe also possible as background color expression:

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

regards

Marco

Re: Alternative for a Loop in Chart Expressions

Tue, 15 Mar 2016 10:21:32 GMT

Thanks Marco,

It's a nice option

Re: Alternative for a Loop in Chart Expressions

Tue, 15 Mar 2016 10:23:15 GMT

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

Re: Alternative for a Loop in Chart Expressions

Anonymous — Tue, 15 Mar 2016 10:39:29 GMT

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

;

Re: Alternative for a Loop in Chart Expressions

Tue, 15 Mar 2016 10:48:05 GMT

Just Perfect Bill Many thanks!!!

Re: Alternative for a Loop in Chart Expressions

Anonymous — Tue, 15 Mar 2016 11:38:19 GMT

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.