8 Replies Latest reply: Jun 20, 2016 11:38 PM by lingling Guo RSS

    Filter with expression

    lingling Guo

      Hi Everyone,

       

      i try to put this in the table, but it said invalid dimension, please help me:)

       

       

      if(GetFieldSelections(Period)='3 Mths', [Start Date] = {"$(='>=' & Date(today(2), 'D/M/YYYY'))"}, 'D/M/YYYY'), [Start Dates])

        • Re: Filter with expression
          Stefan Wühl

          This part is invalid syntax (partly looks like a field modifier of a set expression, which can only be used in aggregation functions, partly like a format code for a date() function)

          [Start Date] = {"$(='>=' & Date(today(2), 'D/M/YYYY'))"}, 'D/M/YYYY')

           

          I assume you want to use this as calculated dimension?

           

          Maybe like

           

          =if(GetFieldSelections(Period)='3 Mths', If( [Start Date] >= today(2), [Start Dates]))


          Or using in an expression with an assumed COUNT aggregation function:


          =if(GetFieldSelections(Period)='3 Mths',

          Count( {< [Start Date] = {"$(='>=' & Date(today(2), 'D/M/YYYY'))"} >} [Start Dates])

          )

            • Re: Filter with expression
              lingling Guo

              Hi Swuehl,

               

              Thank you so much, I have actually create a drop box list, and I would like to connect it to my data:

              for example, when I select '3 Mths', the "Start dates" will filter and shows dates that is within next 3 month, but my code fails:

              Capture.PNG

               

              if(GetFieldSelections(Period)='3 Mths',[Start Date] = {'$(='>=' & Date(today(2), 'D/M/YYYY') &'<=' & Date(AddMonths(today(2),3), 'D/M/YYYY'))'}

              if(GetFieldSelections(Period)='6 Mths',[Start Date] = {'$(='>=' & Date(today(2), 'D/M/YYYY') &'<=' & Date(AddMonths(today(2),6), 'D/M/YYYY'))'}

              if(GetFieldSelections(Period)='9 Mths',[Start Date] = {'$(='>=' & Date(today(2), 'D/M/YYYY') &'<=' & Date(AddMonths(today(2),9), 'D/M/YYYY'))'}

              if(GetFieldSelections(Period)='1 Yr',[Start Date] = {'$(='>=' & Date(today(2), 'D/M/YYYY') &'<=' & Date(AddMonths(today(2),12), 'D/M/YYYY'))'}

              if(GetFieldSelections(Period)='2 Yrs',[Start Date] = {'$(='>=' & Date(today(2), 'D/M/YYYY') &'<=' & Date(AddMonths(today(2),24), 'D/M/YYYY'))'}, [Start Date])))))