Filter to exclude current months data

[podge2019]

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.

[podge2019]

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.

[sunny_talwar · Accepted solution]

How about this

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

[podge2019]

@sunny_talwar 

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

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

Still includes current month.

[sunny_talwar]

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

[podge2019]

@sunny_talwar  no problem.

Many thanks for trying.

[Lech_Miszkiewicz]

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

[podge2019]

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.

[Lech_Miszkiewicz]

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..

[podge2019]

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.