8 Replies Latest reply: May 19, 2015 3:20 AM by James Kerrigan

# Trouble with if & count statements in KPI chart

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?

• ###### Re: Trouble with if statements

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

• ###### Re: Trouble with if statements

Sorry I probably didn't explain it very well.

So I have a table like so

ColourLowHigh
Red60100
Blue4080
Green2030
Black60180
Red1020
Blue80100
Green50140
Black8090

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.

• ###### Re: Trouble with if statements

Hi James,

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

HTH Andy

• ###### Re: Trouble with if statements

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

• ###### Re: Trouble with if statements

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

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

• ###### Re: Trouble with if & count statements in KPI chart

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?

• ###### Re: Trouble with if & count statements in KPI chart

Perhaps this:

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

• ###### Re: Trouble with if & count statements in KPI chart

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!