topic Re: if expression within another if in QlikView

if expression within another if

Wed, 08 Jan 2014 14:06:21 GMT

I'm trying to put multiple conditions "if" inside another "if", where this condition have values in the years 2012, 2013 and 2014, but if 2014 is zero the formula should be made using the values of 2012 and 2013, put up in 2014 is not zero the formula must be made using 2013 and 2014.

As follows:

if(if(ANO=year(today()),VALOR_CONTABIL=0,sum(if(ANO=year(today())-1,VALOR_CONTABIL))/sum(if(ANO=year(today())-2,VALOR_CONTABIL)))-1), (sum(if(ANO=year(today()),VALOR_CONTABIL))/sum(if(ANO=year(today())-1,VALOR_CONTABIL)))-1)

Re: if expression within another if

jerem1234 — Wed, 08 Jan 2014 14:23:21 GMT

Maybe this would do?

=if(sum({<ANO = {$(=year(today()))}>}VALOR_CONTABIL)=0,sum({<ANO = {$(=year(today())-1)}>}VALOR_CONTABIL)/sum({<ANO= {$(=year(today())-2)}>}VALOR_CONTABIL)-1,

sum({<ANO = {$(=year(today()))}>}VALOR_CONTABIL)/sum({<ANO = {$(=year(today())-1)}>}VALOR_CONTABIL)-1)

If not, could you post a sample qvw demonstrating your problem or some mock data?

Might be just making adjustments to this equation here.

Hope this helps!

Re: if expression within another if

mjm — Wed, 08 Jan 2014 16:12:01 GMT

Hello Vinicius,

I came across this website which helps with generating nested IF statements.

http://www.qlikblog.at/tools/Nested-If-Generator.html

I hope this helps. Please mark as helpful if so.

Thanks,

Re: if expression within another if

Wed, 08 Jan 2014 17:34:35 GMT

Jerem

Esta formula funciona, porem se eu selecionar 2012 e 2013, a formula deveria entender que 2014 inteiro é nulo, pois não foi selecionado, mas a formula está entendendo que tem numero, pois conta no BD informação para 2014.

=if(sum({<ANO={$(=year(today()))}>} VALOR_CONTABIL)=0,sum({<ANO={$(=year(today())-1)}>} VALOR_CONTABIL)/sum({<ANO={$(=year(today())-2)}>} VALOR_CONTABIL)-1,

sum({<ANO={$(=year(today()))}>} VALOR_CONTABIL)/sum({<ANO={$(=year(today())-1)}>} VALOR_CONTABIL)-1)

Re: if expression within another if

Wed, 08 Jan 2014 17:34:56 GMT

Não consegui utilizar este link

Re: if expression within another if

jerem1234 — Wed, 08 Jan 2014 17:40:27 GMT

Maybe add an extra criteria to your if statement like you had before:

=if(sum({<ANO = {$(=year(today()))}>}VALOR_CONTABIL)=0 and ANO=year(today()),sum({<ANO = {$(=year(today())-1)}>}VALOR_CONTABIL)/sum({<ANO= {$(=year(today())-2)}>}VALOR_CONTABIL)-1,

sum({<ANO = {$(=year(today()))}>}VALOR_CONTABIL)/sum({<ANO = {$(=year(today())-1)}>}VALOR_CONTABIL)-1)

Hope this helps!

Re: if expression within another if

Wed, 08 Jan 2014 18:11:46 GMT

With this condition the formula is locked in the current year, the need is it to do the calculation for the mobile year.

I choose the year you want to calculate the% if I do not choose the formula considers the condition of formula

X 2012 2013 2014 Formula (IF)%

Jan 10 20 30 50%

Feb 9 18 100%

Mar 8 15 87.5%

Apr 11 8 -27.3%

May 12 5 -58.3%

if in Excel formula would be:

= IF (D13 = 0; (C13/B13) -1; (D13/C13) -1)

where:

Column B = 2012

Column C = 2013

Column D = 2014

But Qlik this formula should respect the national team of the year, if I select 2012 and 2013 to formulate the month JAN return 100%

Re: if expression within another if

jerem1234 — Wed, 08 Jan 2014 18:44:27 GMT

Here try this formula out:

=if(sum({<ANO = {$(=max(ANO))}>}VALOR_CONTABIL)=0,sum({<ANO = {$(=max(ANO)-1)}>}VALOR_CONTABIL)/sum({<ANO= {$(=max(ANO)-2)}>}VALOR_CONTABIL)-1,

sum({<ANO = {$(=max(ANO))}>}VALOR_CONTABIL)/sum({<ANO = {$(=max(ANO)-1)}>}VALOR_CONTABIL)-1)

Hopefully this one does the job.

Re: if expression within another if

Wed, 08 Jan 2014 19:44:17 GMT

One part went well, however if I select 2012 x 2014 or 2013 x 2014, the formula has to respect the national team, and is not happening. Another thing, if the selection is a single year "2012", the formula does not return anything, because the result is zero.

Re: if expression within another if

jerem1234 — Wed, 08 Jan 2014 20:27:45 GMT

So if you select a single year, say 2012, then since you only selected one year it should always be 0?

If you select 2012 and 2014, then this should do sum(2014)/sum(2012) if 2014 is not zero, else it should be 0?

Not sure how you want the formula to behavior with your selections.

Can you give me the scenarios of what you want when:

2012 is selected.

2012 and 2014 is selected

2013 and 2014 is selected

Else if you want to post sample qvw with dummy data that demonstrates your problem, I would be able to help you quicker.

Re: if expression within another if

Thu, 09 Jan 2014 11:18:55 GMT

Let the answer to a solution

Selecting an isolated year 2012 or 2013 or 214, the return shall be zero (0)

Selecting 2012 x 2013, the formula does the calculation between (2013/2012),

Selecting 2012 x 2014, the formula does the calculation between (2014/2012),

Selecting 2013 x 2014, the formula does the calculation from 2014/2013.

Re: if expression within another if

jerem1234 — Thu, 09 Jan 2014 18:59:23 GMT

Try this one and see if it fits your needs.

=if(GetSelectedCount(ANO)=0 or GetSelectedCount(ANO)>2,

if(sum({<ANO = {$(=max(ANO))}>}VALOR_CONTABIL)=0,sum({<ANO = {$(=max(ANO)-1)}>}VALOR_CONTABIL)/sum({<ANO= {$(=max(ANO)-2)}>}VALOR_CONTABIL)-1,sum({<ANO = {$(=max(ANO))}>}VALOR_CONTABIL)/sum({<ANO = {$(=max(ANO)-1)}>}VALOR_CONTABIL)-1),

if(sum({<ANO = {$(=max(ANO))}>}VALOR_CONTABIL)<>0, sum({<ANO = {$(=max(ANO))}>}VALOR_CONTABIL)/sum({<ANO= {$(=min(ANO))}>}VALOR_CONTABIL)-1, 0))

Hope this helps!

Re: if expression within another if

Thu, 09 Jan 2014 20:16:30 GMT

Thank you friend.

This formula did the calculation correctly.

Re: if expression within another if

Fri, 10 Jan 2014 16:38:21 GMT

Jerem friend

There's one more detail that this formula must include, in addition to any conditions that the formula considers to do the calculations, we have one more:

Totals (2012, 2013, 2014) are classified by segment, ie, segment 1, 2, 3. Each one means a product line, and the formula has to respeirar segment included in the formula to make all other calculations.

example:

='TOTAL: '&Num(if(LINHA='2', IF(GetSelectedCount(ANO)=0 or GetSelectedCount(ANO)>2,

if(sum({<ANO={$(=max(ANO))}>}QTDE_LITROS_CAIXA/100)=0,

(sum({<ANO={$(=max(ANO)-1)}>}QTDE_LITROS_CAIXA/100)/sum({<ANO={$(=max(ANO)-2)}>}QTDE_LITROS_CAIXA/100)-1)*100,

(sum({<ANO={$(=max(ANO))}>}QTDE_LITROS_CAIXA/100)/sum({<ANO={$(=max(ANO)-1)}>}QTDE_LITROS_CAIXA/100)-1)*100),

if(sum({<ANO={$(=max(ANO))}>}QTDE_LITROS_CAIXA/100)<>0,

(sum({<ANO={$(=max(ANO))}>}QTDE_LITROS_CAIXA/100)/sum({<ANO={$(=min(ANO))}>}QTDE_LITROS_CAIXA/100)-1)*100,0)),0),'##,#')&'%'

Re: if expression within another if

Fri, 10 Jan 2014 16:39:55 GMT

In this example if I do not select any segment the number is zero, however the formula should respect the segment in-built in it and return the correct calculation.

Re: if expression within another if

Sat, 11 Jan 2014 11:49:21 GMT

Good morning, already managed to fix this formula.

See how it fits:

='TOTAL: '&Num (if(GetSelectedCount(ANO)=0 or GetSelectedCount(ANO)>2,

if(sum({<ANO={$(=max(ANO))}>}IF(LINHA='2',QTDE_LITROS_CAIXA/100))=0,

(sum({<ANO={$(=max(ANO)-1)}>}IF(LINHA='2',QTDE_LITROS_CAIXA/100))/sum({<ANO={$(=max(ANO)-2)}>}IF(LINHA='2',QTDE_LITROS_CAIXA/100))-1)*100,

(sum({<ANO={$(=max(ANO))}>}IF(LINHA='2',QTDE_LITROS_CAIXA/100))/sum({<ANO={$(=max(ANO)-1)}>}IF(LINHA='2',QTDE_LITROS_CAIXA/100))-1)*100),

if(sum({<ANO={$(=max(ANO))}>}IF(LINHA='2',QTDE_LITROS_CAIXA/100))<>0,

(sum({<ANO={$(=max(ANO))}>}IF(LINHA='2',QTDE_LITROS_CAIXA/100))/sum({<ANO={$(=min(ANO))}>}IF(LINHA='2',QTDE_LITROS_CAIXA/100))-1)*100,0)),'##,##')&'%'