Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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?

Thanks in advance.

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

Perhaps this:

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

8 Replies
ToniKautto
Employee
Employee

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

Not applicable
Author

Hi Toni, thanks for your reply.

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.

awhitfield
Partner - Champion
Partner - Champion

Hi James,

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

HTH Andy

Not applicable
Author

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

Not applicable
Author

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

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

Not applicable
Author

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
Partner - Champion III
Partner - Champion III

Perhaps this:

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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!