Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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()),
)
)
)
)
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?
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))