how to build a filter with comparison on dates with prompts

First of all you have to build two prompts yearmonth_TO and yearmonth_FROM that you will use to select from list or write the text of the ID and a prompt n_month to enter the number of months you want to analyze.


You have to create a filter using "advanced qualify" and then use those strings:


Filter with prompts on date between:
ApplyComparison ("#0 is not null AND #0  BETWEEN #E1 and #E2"; yearmonth@ID; ?[ yearmonth_TO]; ?[ yearmonth_FROM])



Comparison last 12 months:

ApplyComparison (" #0 between  to_number(to_char(add_months(to_date(#E1, 'yyyymm'), - 11 ), 'yyyymm')) and #E1 ";  yearmonth@ID; ?[ yearmonth_TO ])

From date entered to date entered + n months:

ApplyComparison (" #0 between  #E1 and to_number(to_char(add_months(to_date(#E1, 'yyyymm'), + #E2 -1), 'yyyymm'))  ";  yearmonth@ID; ?[ yearmonth_FROM ] ; ?[n_month])