Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
abhaytrip
Contributor

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

Re: Max Date minus 1 month Data

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
Highlighted
Chanty4u
Esteemed Contributor III

Re: Max Date minus 1 month Data

try

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

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

Highlighted
MVP
MVP

Re: Max Date minus 1 month Data

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)

Highlighted
YoussefBelloum
Esteemed Contributor

Re: Max Date minus 1 month Data

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';

Highlighted
Chanty4u
Esteemed Contributor III

Re: Max Date minus 1 month Data

Edited

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

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

Highlighted
MVP
MVP

Re: Max Date minus 1 month Data

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.

Highlighted
Chanty4u
Esteemed Contributor III

Re: Max Date minus 1 month Data

thanks Manish  Noted !

Highlighted
big_dreams
Contributor III

Re: Max Date minus 1 month Data

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

Re: Max Date minus 1 month Data

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

Highlighted
abhaytrip
Contributor

Re: Max Date minus 1 month Data

Thanks , this did work.