topic Re: Filter to exclude current months data in Qlik NPrinting

Filter to exclude current months data

podge2019 — Wed, 13 May 2020 13:11:34 GMT

Hello,

New to NPrinting so for some this should hopefully be quiet easy.

Looking to apply a filter that will exclude my current months data in a report and just give me the last two months. i.e. exclude this month (May) and just give April and March.

Formula I am currently using below (thanks to @Lech_Miszkiewicz NPrinting Adventures Blog) gives me May, April, March.

Advanced Search =([Date]>MonthStart(Today(),-2))*([Date]<=Today())

Would someone be able to advise how the above would be amended to exclude this current month.

Many Thanks in Advance.

Re: Filter to exclude current months data

sunny_talwar — Wed, 13 May 2020 13:27:14 GMT

May be this

=([Date]>MonthStart(Today(),-2))*([Date]<MonthStart(Today()))

Re: Filter to exclude current months data

podge2019 — Wed, 13 May 2020 13:53:27 GMT

Thanks for the quick reply Sunny @sunny_talwar ,

Unfortunately

=([Date]>MonthStart(Today(),-2))*([Date]<MonthStart(Today()))

still brings in the three months data.

It does not exclude the current month.

Re: Filter to exclude current months data

sunny_talwar — Wed, 13 May 2020 14:03:03 GMT

How about this

=[Date] > MonthStart(Today(), -2) and [Date] < MonthStart(Today())

Re: Filter to exclude current months data

podge2019 — Wed, 13 May 2020 14:36:06 GMT

Hi Sunny, @sunny_talwar

No change also with:

=[Date] > MonthStart(Today(), -2) and [Date] < MonthStart(Today())

Still bringing in this months data.

Is there any more information I could provide that may assist?

Thanks again.

Re: Filter to exclude current months data

sunny_talwar — Wed, 13 May 2020 14:39:51 GMT

Can you try out few things. What do you get when you use just this

=[Date] < MonthStart(Today())

Do you get all months before May? or do you still get May? How about when you use this

=[Date] <= (MonthStart(Today())-1)

Re: Filter to exclude current months data

podge2019 — Wed, 13 May 2020 14:52:21 GMT

Hi Sunny, @sunny_talwar

When I used: =[Date] < MonthStart(Today())

I get all data that is loaded so it goes back to Jan 2017 up to and including May 2020.

When I use: =[Date] <= (MonthStart(Today())-1)

I get the same - Jan 2017 up to and including May 2020.

Regards

Re: Filter to exclude current months data

sunny_talwar — Wed, 13 May 2020 14:56:50 GMT

Sorry, can you try one more thing for me... what do you get when you use this

=[Date] < MonthStart(Today(), -1)

Anything different? Also, does spaces make a difference?

=[Date]<MonthStart(Today(), -1) =[Date]<MonthStart(Today())

Re: Filter to exclude current months data

podge2019 — Wed, 13 May 2020 15:06:47 GMT

Happy to try @sunny_talwar

=[Date] < MonthStart(Today(), -1)

This bring in all the data from Jan 2017 to April 2020, however, it then doesn't populate the data in April 2020.

Does exclude May 2020 though which is progress so the spaces make a difference.

Re: Filter to exclude current months data

sunny_talwar — Wed, 13 May 2020 15:22:03 GMT

Is April 2020 included or not included? I am not sure I understand

Re: Filter to exclude current months data

podge2019 — Wed, 13 May 2020 15:31:42 GMT

Sorry if I was not clear @sunny_talwar

April (the month) is included, however, the data for April is not populated.

Please see the screenshot I've attached of the results.

Hopefully you can see it.

Re: Filter to exclude current months data

sunny_talwar — Wed, 13 May 2020 15:37:52 GMT

How about this

=[Date] < MonthEnd(Today(), -1)

Re: Filter to exclude current months data

podge2019 — Wed, 13 May 2020 15:40:49 GMT

@sunny_talwar

=[Date] < MonthEnd(Today(), -1)

This brings back all the data from Jan 2017 to May 2020.

Still includes current month.

Re: Filter to exclude current months data

sunny_talwar — Wed, 13 May 2020 15:51:19 GMT

Sorry, I am just confused why it is doing this. This is a very strange behavior which I am not able to explain.

Re: Filter to exclude current months data

podge2019 — Wed, 13 May 2020 17:05:13 GMT

@sunny_talwar no problem.

Many thanks for trying.

Re: Filter to exclude current months data

Lech_Miszkiewicz — Wed, 13 May 2020 23:14:34 GMT

Hi,

We obviously don't see full picture, full data model, technics used and expressions used in charts.

Expressions mentioned by @sunny_talwar are correct and he is rightly surprised why they don't work. Can you dummy up your data (except date field) and attach sample of your qvf file. We should be able to investigate more then

Re: Filter to exclude current months data

podge2019 — Thu, 14 May 2020 09:23:41 GMT

Hi, @Lech_Miszkiewicz

I'm using QlikView for the data and the QVW is nearly 1 GB in size so I far exceed the size limit here 😞 .

There are also multiple data sources feeding the QVW.

I will just accept I may not be able to do.

Thanks.

Re: Filter to exclude current months data

Lech_Miszkiewicz — Thu, 14 May 2020 10:21:47 GMT

You can always apply selections and use "reduce data" from File menu to reduce size to only few MB...

i leave it up to you..

Re: Filter to exclude current months data

podge2019 — Mon, 18 May 2020 14:09:26 GMT

Hi, @sunny_talwar @Lech_Miszkiewicz

Managed to figure this one out...kinda the long way around.

The QVW already had a variable set - Let vMonthEnd = num(MonthStart(AddMonths(Today(), -1)))

This wasn't set using "DATE" but under Master Calendar as num(date#(Fiscal_Year_Month,'YYYY-MM')) as Fiscal_Year_Month_Num,

Instead of doing an advanced search, I used an evaluate search in my NPrinting filter on Fiscal_Year_Month_Num rather than date.

The above then returned just Aprils data with the Evaluate Value Filter -> =$(vMonthEnd)

Created another Evaluate variable then - Let vMonthEnd1 = num(MonthStart(AddMonths(Today(), -2)))

Applied both of these and it returned March and April data correctly for me along with filters for Country and Client.

Cant thank you enough for putting me on the right path and hope the above makes sense.