Trouble with if & count statements in KPI chart

[#1]

Hey guys,

I'm trying to do a couple things with the If statement which I'm having trouble with, but will replace dimensions with colours to simplify.

I'm trying to count how many colours have a difference higher than 50% and put this in a KPI chart. So if here are 3 out of 4 colours with a % over 50%, I want it to say 3.

Each colour has activities associated with it, and each activity has a low and a high number. I divide low by high to work out a difference, and I want to count how many colours have a difference higher than 50%. So if the colour Red has 5 activities, with these totalling 100 for high and 60 for low, this would be 60%.

So the logic should be something like

If(Sum(Low) / (Sum(High) > 0.5, Count(Colour)

But that doesn't work for obvious reasons, because I want it for each individual colour, not the total.

Does anyone know how to calculate this?

Thanks in advance.

[ToniKautto]

I am not completely sure I understand your scenario. Could you provide s simple sample to show how you set this up?

[#3]

Hi Toni, thanks for your reply.

Sorry I probably didn't explain it very well.

So I have a table like so

Colour	Low	High
Red	60	100
Blue	40	80
Green	20	30
Black	60	180
Red	10	20
Blue	80	100
Green	50	140
Black	80	90

and I want to count how many colours have a % difference greater than 50% (Total Low / Total High). For example, Red would.

Red: Low = 60 + 10, High = 100 + 20

                = 70 / 120

                = 58%

So the count would be at least 1.

I don't want to calculate the %  difference for each individual row, but rather the total, and I'm unsure of what syntax to use.

[awhitfield]

Hi James,

Check out the attached example, think it's what you are after

HTH Andy

[#5]

Hi Andrew, thanks for that! Unfortunately I don't have Qlik View installed, only Qlik Sense. When I opened up the app it only the fields were present, nothing else.

Do you mind posting the syntax you used? Or someone else with QV? I greatly appreciate the help

[#6]

Using a chart, put color  in dimension and following expression in measures:

if(Sum(Low)/ Sum(High) > 0.5,1,0)

[#7]

Sorry I should have clarified better, but I want to count this for a KPI chart, so there is no dimension.

Is there anyway that equation can be changed slightly so it's suitable for a KPI?

[jonathandienst · Accepted solution]

Perhaps this:

=Sum(Aggr(If((Sum(Low) / Sum(High)) > 0.5, 1, Colour))

[#9]

Works a beauty thanks mate!

You were missing a paranthesis which I changed and it works great.

Sum(Aggr(If(Sum(Low) / Sum(High) >= 0.5, 1), Colour))

Cheers!