3 Replies Latest reply: Mar 22, 2017 10:57 AM by Avinash R RSS

    If Condition on Year in Variable

    Shashank Vyas

      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.

        • Re: If Condition on Year in Variable
          Avinash R

          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
              Shashank Vyas

              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
                  Avinash R

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