4 Replies Latest reply: Apr 15, 2018 10:50 PM by Sasidhar Parupudi RSS

    if Statement based on possible field selection

    Dan Coen

      Hello, I am trying to create an if expression that considers the possibility of a field being selected. See below

       

      I would like the expression to evaluate based on the context of the month(s) selected. For example, if two prior months are selected, simply add the full amount of the months. But if the current month and a prior month is selected,I need the expression to only add the current month to date sum and the full prior month. These sums are based on prior year data so if you dont specify "to date", it adds the full amount for the month.

       

      I created a variable called (vCurrentPeriodName) that always returns the current month (in this case, "Apr")

       

      I need the expression to recognize when April (current month) is one of the possible selections and perform the first measure in the if statement. If not, 2nd measure.

       

      if(GetSelectedCount([Fiscal - Month])>1,

      Sum({$<[Fiscal - Flag Prior Month YTD]={'Y'}>}[FY17 Baseline Amount])

      +

      Sum({$<[Fiscal - Period Name]-={'(vCurrentPeriodName)'}>}[FY17 Baseline Amount])

      ,

      Sum([FY17 Baseline Amount]))

       

      Any thoughts?

        • Re: if Statement based on possible field selection
          Krishna Nagulapally

          Can you please post some sample data?

          unless i have data and verify it,i cannot confirm the below expression works.

           

          u can give this expression a try:

           

          if(GetSelectedCount([Fiscal - Month])>1,

           

             if( 

               match(concat(getfieldselection([Fiscal - Month]) ,',' ) ,'$(vCurrentPeriodName)') =1 ,

                       Sum({$<[Fiscal - Flag Prior Month YTD]={'Y'}>}[FY17 Baseline Amount])

                           +

                          Sum({$<[Fiscal - Period Name]-={'(vCurrentPeriodName)'}>}[FY17 Baseline Amount])

              )

          ,

          Sum([FY17 Baseline Amount]))

          • Re: if Statement based on possible field selection
            Dan Coen

            Actually, having an issue where it is not finding the (vCurrentPeriodName)) as a possible value in the selections. For example, using the below expression....

             

            match(concat(getfieldselections([Fiscal - Period Name]),',') ,(vCurrentPeriodName))

             

            ...if April is selected, then the expression works since we are in the current month. 1.jpg

             

            However, if I selected April and October, it does not work.

            Multi.jpg

             

            Any ideas? I need the expression to identify the current month in all the selections (in this case, Apr), and return true.

            • Re: if Statement based on possible field selection
              Sasidhar Parupudi

              May be this?

               

              if(GetSelectedCount([Fiscal - Month])>1 AND Index(GetFieldSelections([Fiscal - Month],','),'(vCurrentPeriodName)')>0,

              Sum({$<[Fiscal - Flag Prior Month YTD]={'Y'}>}[FY17 Baseline Amount])

              +

              Sum({$<[Fiscal - Period Name]-={'(vCurrentPeriodName)'}>}[FY17 Baseline Amount])

              ,

              Sum([FY17 Baseline Amount]))