if expression within another if

[#1]

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)

[jerem1234]

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!

[mjm · Accepted solution]

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,

[#4]

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)

[#5]

Não consegui utilizar este link

[jerem1234]

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!

[#7]

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%

[jerem1234]

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.

[#9]

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.

[jerem1234]

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.