Actions >Selection >Select in Field

[Anonymous]

Hi,

I am using a textbox to execute an action, for that specific textbox I have created an action that will filter records from last year, according to the YRMON field that is displayed as '2017-01'

I need to make the calculation automatically, so in February I need to display all the records from February 2016 (2016-02), in march I need to display all the records from March and so.

I have tried this select in field:

=Date#(YRMON, 'YYYY-MM') > MonthStart(Today(), -13)

But it's not working.

Any idea?

[sunny_talwar]

May be like this

Field

YRMON

Search String

Date(MonthStart(Today(), -12), 'YYYY-MM')

[Anonymous]

Didn't work, I need all the YRMON from one year from now. But that expression only displays the data for the YRMON = Today -12

I need all the other months between today and today-12

Please advise.

[sunny_talwar]

May be this as search string

='<=' & Date(MonthStart(Today(), -12), 'YYYY-MM')

[Anonymous]

The expression looks correct, but it's not doing anything.

[sunny_talwar]

Can you share a screenshot for listbox of YRMON field?

[Anonymous]

Sure,

[sunny_talwar]

Your YRMON field is read as text... you need to fix this when you load the data

LOAD Date(MonthStart(Date#(YRMON, 'YYYY-MM')), 'YYYY-MM') as YRMON

Once this is done, ='<=' & Date(MonthStart(Today(), -12), 'YYYY-MM') this should work

[Anonymous]

I have the following script:

LOAD *, Mid(YRMON,1,4) as YR_Cal,Mid(YRMON,6,2) as Month, Mid(FYQTR,3,4) as FY, Mid(FYQTR,9,1) as QTR, Mid(FYQTR, 3, 4) * 4 + Right(FYQTR, 1) as QTRCounter;

If I add:

LOAD *, Mid(YRMON,1,4) as YR_Cal,Mid(YRMON,6,2) as Month, Mid(FYQTR,3,4) as FY, Mid(FYQTR,9,1) as QTR, Mid(FYQTR, 3, 4) * 4 + Right(FYQTR, 1) as QTRCounter, Date(MonthStart(Date#(YRMON, 'YYYY-MM')), 'YYYY-MM') as YRMON;

I got an OLEDB error, am I doing something wrong?

[sunny_talwar]

Try this:

LOAD *,

          Mid(YRMON,1,4) as YR_Cal,

          Mid(YRMON,6,2) as Month,

          Mid(FYQTR,3,4) as FY,

          Mid(FYQTR,9,1) as QTR,

          Mid(FYQTR, 3, 4) * 4 + Right(FYQTR, 1) as QTRCounter,

          Date(MonthStart(Date#(YRMON, 'YYYY-MM')), 'YYYY-MM') as New_YRMON;

and now trigger like this

Field

New_YRMON

Search String

Same as before