Max Date minus 1 month Data

[abhaytrip]

Hi guys ,

Im facing an issue where I have Date in the qvd from 1/1/2017 to 3/1/2018 in the format MM/DD/YYYY.

but in qlikview I need to show Data only till Feb , 2018 that is (2/1/2018).

from 1/1/2017 to 2/1/2018

So thats basically excluding the previous month data i need to represent it considering that im in the month of April now.

What ways is this possible for me to go ahead with ?

Thanks

[Chanty4u]

try

WHERE year(Datefield)=year(now())

AND month(Datefield)=month(now())-1;

[MK_QSL]

Considering you are in April month and you want to show data from Year start to Feb End..

Use

SUM({<YourDateField = {">=$(=YearStart(Today()))<=$(=MonthEnd(Today(),-2))"}>}Sales)

or

SUM({<YourDateField = {">=$(=YearStart(Max(YourDateField)))<=$(=MonthEnd(Max(YourDateField),-2))"}>}Sales)

[YoussefBelloum]

Hi,

if it is really static (always < 3/1/2018), you can try this on the script, at the end of your table LOAD:

table:

LOAD

.

.

.

FROM...

WHERE DATE_FIELD <'3/1/2018';

[Chanty4u]

Edited

WHERE year(Datefield)=year(now())

AND month(Datefield)=month(now())-2;

[MK_QSL]

Make sure that you don't get performance and load time issue. Because you are using where clause while loading data from QVD which will make your QVD load unoptimized.

For small data set it is ok but for QVDs having millions of data, this is not preferable.

[Chanty4u]

thanks Manish  Noted !

[big_dreams]

Hi mrkachhiaimp,

I am totally agree if we use where clause on qvd then load become unoptimize. But it will reduced your data at script level which will help to actual end user to get faster output compare to load full optimize data and then write set analysis on front end.

Which one is better approach of designing entire dashboard?

Regards,

max

[big_dreams · Accepted solution]

Why you are using 2 fields?? If we can achieve it one field

like

where dateField<=date(MonthStart(Today(),-2))

Regards,

[abhaytrip]

Thanks , this did work.