Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Please visit COVID-19 group to keep up the discussion. GO TO GROUP
Highlighted
Not applicable

Filter to only display todate as XMMDD

I am needing a way to filter data by date.

Currently trying

Let lastrefresh = now ();

XMMDD

From this I need to create a filter to filter to equal X1212.

tomorrow it should equal x1213

1 Solution

Accepted Solutions
Highlighted

Re: Filter to only display todate as XMMDD

So its not a x, but a # in front of the date?

May be this:

LET vFilter = '#' & Date(Today(), 'MMDD');


LIB CONNECT TO 'pharmoresql production (pharmore_rwinkel)';


LOAD FacID,
PSID,
    RxBatch,
    RxNo,
    DispenseDt,

FROM Rx.dbo.Rxs

Where RxBatch = '$(vFilter)';

View solution in original post

7 Replies
Highlighted

Re: Filter to only display todate as XMMDD

May be this:

'x' & Date(Today(), 'MMDD')

Highlighted
Not applicable

Re: Filter to only display todate as XMMDD

This is were I have it but not sure whee to put the statement you gave me?

SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';

Let lastrefresh = now ();

LIB CONNECT TO 'pharmoresql production (pharmore_rwinkel)';

LOAD FacID,
PSID,
    RxBatch,
    RxNo,
    DispenseDt,

Highlighted

Re: Filter to only display todate as XMMDD

What is your date field in the above script you have posted?

Highlighted
Not applicable

Re: Filter to only display todate as XMMDD

I am not trying to match the date field. we name our RxBatch X1212  the 1212 is our naming convention we use.

The last refresh part is only to get to the MMDD

No really sure how the or where the Let lastrefresh = now();

This report refreshes every 5 minutes.

If we could only pull in RXbatch XMMDD would be great or use it as a filter pane on the sheet would be ok also.

Highlighted

Re: Filter to only display todate as XMMDD

I am not sure what you are looking to do, but just guessing...

LET vFilter = 'x' & Date(Today(), 'MMDD');

LIB CONNECT TO 'pharmoresql production (pharmore_rwinkel)';

LOAD FacID,
PSID,
    RxBatch,
    RxNo,
    DispenseDt,

FROM ....

Where RxBatch = '$(vFilter)';

Highlighted
Not applicable

Re: Filter to only display todate as XMMDD

When I run this, it does not load any data.

LET vFilter = 'x' & Date(Today(), 'MMDD');


LIB CONNECT TO 'pharmoresql production (pharmore_rwinkel)';


LOAD FacID,
PSID,
    RxBatch,
    RxNo,
    DispenseDt,

FROM Rx.dbo.Rxs

Where RxBatch = '$(vFilter)';

This is what my data looks like.  I am trying to out everything but the #MMDD

So the ones in the red circle should be the only thing being pulled.

Highlighted

Re: Filter to only display todate as XMMDD

So its not a x, but a # in front of the date?

May be this:

LET vFilter = '#' & Date(Today(), 'MMDD');


LIB CONNECT TO 'pharmoresql production (pharmore_rwinkel)';


LOAD FacID,
PSID,
    RxBatch,
    RxNo,
    DispenseDt,

FROM Rx.dbo.Rxs

Where RxBatch = '$(vFilter)';

View solution in original post