topic Re: If Condition on Year in Variable in QlikView

If Condition on Year in Variable

shanky1907 — Wed, 22 Mar 2017 13:16:28 GMT

Hello All,

I have a Problem with one of my variable:

Problem Scenario:

I have an Inline field as Month : Which is Horizontal Dimension in my pivot chart

Month
M
M-1
M-2

and a Calendar month field as

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sept

Oct

Nov

Dec

and Year as well

So, The issue is only when we select 'Feb' or 'Jan' in Month Filter,

I want to set a variable for Year like If i select Jan, then for M the vYear should be Currentyear and for M-1 & M-2 it should be Currentyear-1.

If i select Feb, then for M & M-1 the vYear should be Currentyear and for M-2 it should be Currentyear-1.

Can anyone please help me write the syntax for it.

Re: If Condition on Year in Variable

avinashelite — Wed, 22 Mar 2017 13:51:28 GMT

Some thing like this

vYear

if([Calendar Month]='Jan' and Month='M' ,Year(today()),

if([Calendar Month]='Jan' and (Month='M-1' or Month='M-2' ,Year(today())-1,

if([Calendar Month]='Feb' and Month='M-2' ,Year(today())-1,

if([Calendar Month]='Jan' and (Month='M-1' or Month='M' ,Year(today()),

)

Re: If Condition on Year in Variable

shanky1907 — Wed, 22 Mar 2017 13:57:46 GMT

In this case a selection is made on Calendar Month but not on Month.

So how it will take values from dimension?

I am suing the below:

=If(GetFieldSelections([Calendar Month])='Jan',Year(AddYears(Date#(Year, 'YYYY'), -1)),

If(GetFieldSelections([Calendar Month])='Feb' ,If(Month='M' or 'M-1',Year(AddYears(Date#(Year, 'YYYY'), -1))

),Year))

Also with your query it will only give values for current year. What if we select calendar month & Previous year with Feb?

Re: If Condition on Year in Variable

avinashelite — Wed, 22 Mar 2017 14:57:17 GMT

but you had mentioned as Currentyear rite ? so I had given the above expression

try like this

=If(GetFieldSelections([Calendar Month])='Jan',

if(GetFieldSelections(Year)=1,Year(AddYears(Date#(Year, 'YYYY'), -1)), Year(today())-1)

If(GetFieldSelections([Calendar Month])='Feb' ,

If(Month='M' or 'M-1',

if(GetFieldSelections(Year)=1,Year(AddYears(Date#(Year, 'YYYY'), -1)) Year(today())-1)

Year))