topic Re: if condition in QlikView

if condition

dandaanilreddy — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: if condition

sunny_talwar — Mon, 05 Dec 2016 20:21:41 GMT

May be this:

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

Re: if condition

dandaanilreddy — Mon, 05 Dec 2016 20:29:44 GMT

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

Re: if condition

sunny_talwar — Mon, 05 Dec 2016 20:39:11 GMT

That's what the above expression should do

Re: if condition

jcampbell474 — Mon, 05 Dec 2016 20:42:47 GMT

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.

Re: if condition

dandaanilreddy — Mon, 05 Dec 2016 20:47:07 GMT

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

Re: if condition

sunny_talwar — Mon, 05 Dec 2016 20:54:36 GMT

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.

Re: if condition

dandaanilreddy — Mon, 05 Dec 2016 20:58:36 GMT

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

Re: if condition

sunny_talwar — Mon, 05 Dec 2016 21:00:38 GMT

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

Re: if condition

sunny_talwar — Mon, 05 Dec 2016 21:01:10 GMT

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

Re: if condition

dandaanilreddy — Mon, 05 Dec 2016 21:07:55 GMT

Hi Jason

Thanks for the reply. I have applied your expression but the result is same what i have achieved from sunny's expression. If actual is greater than company plan the goal should be 100%.

Thanks

Anil

Re: if condition

jcampbell474 — Mon, 05 Dec 2016 21:12:08 GMT

Sunny's expression probably encompasses more scenarios than mine, but both of them should work in the example you provided.

Using one row/record, give us an example of the value in these fields:

UnitActuals.ReceivedCasesFromProc: (what is your value)

UnitActuals.BudgetedCases: (what is your value)

Re: if condition

dandaanilreddy — Mon, 05 Dec 2016 21:22:43 GMT

In the above screen shot for Jan 2015 the company plan target is 19365 but we have achieved actuals more than company plan then the goal result should be 100% instead of 4.62%

Thanks

Anil

Re: if condition

dandaanilreddy — Mon, 05 Dec 2016 21:45:48 GMT

I have deleted the expression and recreated it. It's working now thanks.

Thanks

Anil