Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Actions >Selection >Select in Field

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?

9 Replies
sunny_talwar

May be like this

Field

YRMON

Search String

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

Anonymous
Not applicable
Author

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
Not applicable
Author

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

sunny_talwar

Can you share a screenshot for listbox of YRMON field?

Anonymous
Not applicable
Author

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
Not applicable
Author

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