1 Reply Latest reply: Oct 24, 2016 2:38 AM by Lakshmikandh Karthikeyan RSS

    selectMatch() multiple fields

    Aravind Sasidharan

      I have a mashup which allows the user to filter the data by dates using a bunch of custom selection buttons


      YTD, QTD, MTD etc


      Now in the load script I have the following flags defined.


      If(DayNumberOfYear(DATE) <= DayNumberOfYear(Today()), 1, 0 ) as IsInYTD

      If(DayNumberOfQuarter(CREATED_DATE) <= DayNumberOfQuarter(Today()), 1, 0) as IsInQTD,

      (Year(DATE)-Year(Today())) as YearSerial


      As you can see, these flags do not indicate YTD & QTD for the current year but for all the years. I need to keep it this way to help with some set analysis  So to get YTD & QTD for the current year, I have to include YearSerial =0 in the selection.


      I was wondering if there is an option to apply selections on multiple fields using the Field API;


      Some other solutions I could think of is to invoke selectMatch() for the two fields or introduce current year YTD & QTD flags 

      if(InYearToDate(Date, Today(), 0), 1, 0) AS [IsInCurrentYTD],

      if(InQuarterToDate(Date, Today(), 0), 1, 0) AS [IsInCurrentQTD]


      Is there a better solution to achieve the above date filtering behavior in the mashup?