topic Re: If conditions in QlikView

If conditions

Fri, 19 Dec 2014 11:48:27 GMT

Hi All,

I have a table as below.

Name	Type	Amount	Gain%
A	AA	Address1	b
B	BB	address2
C	AA	address3

I need to write a condition for Gain % in a Straight Table (Expression).

pls find the excel condition given below. I need to write the below expression in Qlikview.

b = IF ( OR(address1= "", sum(address1:address3)=0),"", (address1*100)/(address1:address3)

Can anyone help me with this ?

Thanks,

Praveen

Re: If conditions

alkesh_sharma — Fri, 19 Dec 2014 11:54:08 GMT

please share what you want or the logic. Addess1 cannot be added to address2 as mentioned in the Excel expression as these are alpha numric.

More over, The excel formula is not write it has errors!

Re: If conditions

jpenuliar — Fri, 19 Dec 2014 11:55:46 GMT

in simple mathematics, what is the formula of Gain% ?

Re: If conditions

Fri, 19 Dec 2014 12:37:49 GMT

Name	Type	Premium	Gain%
A	AA	1000
B	BB
C	AA	1500
	Totals

I guess this can help you people to understand my question. Actually the column "Premium" may have empty cells also, So If the cell is empty or The total Premium is null, then it should return "Null" or 0. Or it should display a values by calculating Gain %.

Re: If conditions

jpenuliar — Fri, 19 Dec 2014 12:43:52 GMT

So based on your listed columns ( Name,Type,Premium) how do you compute for Gain%?

Re: If conditions

pradnyat — Fri, 19 Dec 2014 12:54:27 GMT

Please find the sample attached file.

Hope this will solve your problem.

Re: If conditions

jpenuliar — Fri, 19 Dec 2014 12:59:05 GMT

formula would look like:

=if(sum(Amount)=0 or sum(total Amount)=0,'',(Amount)/sum(total Amount))

Re: If conditions

Fri, 19 Dec 2014 13:03:40 GMT

Exactly Jonathan. You got it Ryt. The interpretation is ryt.

But how can I write it in Qlikview. I dont have a column or a value for Total Amount.

Re: If conditions

Fri, 19 Dec 2014 13:05:09 GMT

Sorry Pradnya, I couldnt open your file.

Re: If conditions

pradnyat — Fri, 19 Dec 2014 13:06:44 GMT

Okay.

You can use below expression for Gain %:

=if(sum(Amount)=0 or sum(total Amount)=0,'',(Amount)/sum(total Amount))

Re: If conditions

Fri, 19 Dec 2014 13:09:13 GMT

I dont have a column called Total amount.

I need to sum all the amounts.

Re: If conditions

jpenuliar — Fri, 19 Dec 2014 13:12:08 GMT

Total in this case is a Reserved word atatched to Sum() function. without it, the resulting values are per rows

Re: If conditions

jpenuliar — Fri, 19 Dec 2014 13:14:34 GMT

from Qlikview F1( help)

If the word total occurs before an expression, the calculation will be made over all possible values given the current selections, but disregarding the chart dimensions.

Re: If conditions

jpenuliar — Fri, 19 Dec 2014 13:18:18 GMT

if you try it now on qlikview, notice that "Total" is in blue color which means it is a reserved word