Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shanky1907
Creator II
Creator II

If Condition on Year in Variable

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

JanFebMarAprMayJunJulAugSeptOctNovDec

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.

3 Replies
avinashelite

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()),

)

)

)

)

shanky1907
Creator II
Creator II
Author

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?

avinashelite

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))