topic Help with Set analysis in QlikView

Help with Set analysis

Anonymous — Fri, 26 Jun 2015 13:48:56 GMT

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

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 — Fri, 26 Jun 2015 13:54:34 GMT

Try this expression

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

Gysbert_Wassenaar — Fri, 26 Jun 2015 13:56:25 GMT

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 — Fri, 26 Jun 2015 14:00:00 GMT

Yes this works! Thanks!

Anonymous — Fri, 26 Jun 2015 14:03:38 GMT

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 — Fri, 26 Jun 2015 14:03:50 GMT

Could you please mark the question as answered? Thanks

Gysbert_Wassenaar — Fri, 26 Jun 2015 14:18:34 GMT

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