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?