if condition

[dandaanilreddy]

Hello Qlik developers

I have 2 expressions i need to show the percentage if expression sum(actual) is greater than sum(company plan) the goal should be 100% if not it should display the percentage.

Can some one help me please

Thanks

Anil

[sunny_talwar · Accepted solution]

May be this:

Num(RangeMin(Sum(actual)/Sum(company plan), 1), '##.0%')

[dandaanilreddy]

Hi Sunny

Thanks for the reply. I need to show if the actual value is greater then company plan the goal should be 100% if not 100% i need to display the percentage.

actual	company plan	goal(should compare actual with company plan)
1200	900	100%
1000	1200	?
500	400	100%

Thanks

Anil

[sunny_talwar]

That's what the above expression should do

[jcampbell474]

This might be too simple to meet your needs: If(sum(actual)/sum(plan)>=1,1,sum(actual)/sum(plan))

Format the column as Percentage (Properties > Number > Show in Percent.

[dandaanilreddy]

If i apply your expression i am getting the below result. Please see the attachment.

in the below screen shot for jan 2015 the actual is greater than the company plan so the goal showuld be 100% as the actuals value is more than company plan and for sep 2015 the actuals value is less than company plan so for sep 2015 we should display how much % is needed to reach company plan value.

Thanks

Anil

[sunny_talwar]

Anil -

I don't see your expression, but let me explain what I am trying to do.

Num(RangeMin(Sum(actual)/Sum(company plan), 1), '##.0%')

In the above expression, there are two values

1) Sum(actual)/Sum(company plan) ->x

2) 1 -> y

when x> y -> RangeMin(x, y) -> y (when sum(actual)/sum(company plan) > 1 than 1

when x< y -> RangeMin(x, y) -> x (when sum(actual)/sum(company plan) < 1 then sum(actual)/sum(company plan)

Since I cannot see your expressions, I don't know what you are doing, but if you follow my directions above, there is no way you can get a wrong output.

I hope this helps.

[dandaanilreddy]

Here is my expressions

Actuals: =sum(UnitActuals.ReceivedCasesFromProc)

company plan: =Sum(UnitActuals.BudgetedCases)

Goal Expression: Num(RangeMin(Sum(UnitActuals.ReceivedCasesFromProc)/Sum(UnitActuals.BudgetedCases), 1), '##.0%')

Thanks

Anil

[sunny_talwar]

Something is not right my friend. There is no way Goal can have the above expression

Sum(UnitActuals.ReceivedCasesFromProc)/Sum(UnitActuals.BudgetedCases)

will never = to any of those percentages you are getting and neither are they equal to 1. I think you might be mistakenly looking at another expression

[sunny_talwar]

May be add screenshots of your expression's tab or chart properties.