topic Re: Max Date minus 1 month Data in QlikView

Max Date minus 1 month Data

abhaytrip — Fri, 13 Apr 2018 10:01:25 GMT

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

Re: Max Date minus 1 month Data

Chanty4u — Fri, 13 Apr 2018 10:03:36 GMT

try

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

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

Re: Max Date minus 1 month Data

MK_QSL — Fri, 13 Apr 2018 10:05:04 GMT

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)

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

Re: Max Date minus 1 month Data

YoussefBelloum — Fri, 13 Apr 2018 10:05:59 GMT

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

Re: Max Date minus 1 month Data

Chanty4u — Fri, 13 Apr 2018 10:06:59 GMT

Edited

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

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

Re: Max Date minus 1 month Data

MK_QSL — Fri, 13 Apr 2018 10:34:05 GMT

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.

Re: Max Date minus 1 month Data

Chanty4u — Fri, 13 Apr 2018 10:35:36 GMT

thanks Manish Noted !

Re: Max Date minus 1 month Data

big_dreams — Fri, 13 Apr 2018 11:23:25 GMT

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

Re: Max Date minus 1 month Data

big_dreams — Fri, 13 Apr 2018 11:28:08 GMT

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

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

Regards,

Re: Max Date minus 1 month Data

abhaytrip — Fri, 13 Apr 2018 11:35:49 GMT

Thanks , this did work.

Re: Max Date minus 1 month Data

MK_QSL — Fri, 13 Apr 2018 12:07:46 GMT

I am not against reducing data at script end. In fact, this is the right way of handling data so that we need only those records for which we need to do analysis. This will also increase performance due to having lower sized QVW.

But we don't know the size of Future QVD. Say the business grown and now QVD size is in Millions. In that case, I always prefer to use Optimized Load.

You can do something like below to convert that unoptimised load to optimized.

Let vStartDate = Num(YearStart(Today()));
Let vEndDate = Num(MonthEnd(Today(),-2));


TempDates:
Load 
 Date($(vStartDate) + IterNo() - 1) as TempDate 
AutoGenerate 1 
While $(vStartDate) + IterNo() - 1 <= $(vEndDate);


YourFactTable:
Load * From YourQVD Where Exists(TempDate,OrderDate);


Drop Table TempDates;
Let vStartDate = Null();
Let vEndDate = Null();

This way you can reduce date at back end and also take advantage of Optimised QVD Load.

Re: Max Date minus 1 month Data

YoussefBelloum — Fri, 13 Apr 2018 12:15:07 GMT

the secret here is only the use of the "where exist" Right ? mrkachhiaimp‌

Re: Max Date minus 1 month Data

MK_QSL — Fri, 13 Apr 2018 12:22:20 GMT

Yes.. QVD load only allow Alias the field and Where Exists to keep the load optimised.

Re: Max Date minus 1 month Data

big_dreams — Fri, 13 Apr 2018 12:26:49 GMT

Nice trick to achieve optimize load.

But I don't know somehow for me it is not digest-able that to achieve optimize load we have to maintain another temp table.

Leave it that topic, it is always most confusing for me.

What I think when

1: we are focusing on reducing reload time then we go for optimize load.

2: we required better front end user performance we go for unoptimize load or move such condition in extractor level so that my qvd become optimize.

Regards,

max

Re: Max Date minus 1 month Data

big_dreams — Fri, 13 Apr 2018 12:29:52 GMT

and another point which i know about where exist() is

If both field use in exist() having same name then only it will become optimize load else it will not.

so in your example it will still not gave you optimize load.

Regards,

max

Re: Max Date minus 1 month Data

YoussefBelloum — Fri, 13 Apr 2018 12:35:04 GMT

Thanks mrkachhiaimp‌

Re: Max Date minus 1 month Data

YoussefBelloum — Fri, 13 Apr 2018 12:36:25 GMT

This also sounds familiar to me...

Re: Max Date minus 1 month Data

MK_QSL — Fri, 13 Apr 2018 12:51:11 GMT

Agree but in this case, we just have to rename !!