Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Perhaps this:
=Sum(Aggr(If((Sum(Low) / Sum(High)) > 0.5, 1, Colour))
I am not completely sure I understand your scenario. Could you provide s simple sample to show how you set this up?
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.
Hi James,
Check out the attached example, think it's what you are after
HTH Andy
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
Using a chart, put color in dimension and following expression in measures:
if(Sum(Low)/ Sum(High) > 0.5,1,0)
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?
Perhaps this:
=Sum(Aggr(If((Sum(Low) / Sum(High)) > 0.5, 1, Colour))
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!