Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
abhaytrip
Creator
Creator

Max Date minus 1 month Data

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

1 Solution

Accepted Solutions
big_dreams
Creator III
Creator III

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

like

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

Regards,

View solution in original post

17 Replies
Chanty4u
MVP
MVP

try

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

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

MK_QSL
MVP
MVP

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

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

Edited

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

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

MK_QSL
MVP
MVP

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

thanks Manish  Noted !

big_dreams
Creator III
Creator III

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
Creator III
Creator III

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

like

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

Regards,

abhaytrip
Creator
Creator
Author

Thanks , this did work.