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?