Help with Set analysis

[Anonymous]

Simple example : let's say I have a data set with only one column containing colors (red, yellow, blue for instance).

I want to display a table containing :

     -the number of occurence of each color

     -the relative proportion of each color to the red one

ex.

Blue

Red

Red

Blue

Yellow

Blue

would give me

COLOR| Nbr occ | Relative to red

Red      | 2           | 1

Yellow  | 1           | 0,5

Blue     | 3           | 1,5

For the relative proportion to red I tried something like

=count(Color)/count({1 <Color={"Red"}>} Color)

but it gives 0 for the Yellow and Blue row.

What am I doing wrong?

[alexandros17]

Try this expression

Count(colour)/Count({$ <colour={'Red'}>} TOTAL colour)

[Gysbert_Wassenaar]

No idea. It works fine for me. See attached qvw.

Make sure to use the exact case sensitive field name. COLOR, color and Color are three different fields to Qlikview.

[Anonymous · Accepted solution]

Yes this works! Thanks!

[Anonymous]

My point was being able to calculate the total number of Reds for each row, the solution suggested by Alessandro works

Doesn't work :

                         count(Color)/count({1< Color={'Red'}>} Color)

Works :

                         count({$ <Color={'Red'}>} TOTAL Color)

[alexandros17]

Could you please mark the question as answered? Thanks

[Gysbert_Wassenaar]

Oh, you wanted that in a straight/pivot table. Yes, then all you need to do is add the total keyword.