topic Re: YTD Flag Question in QlikView

YTD Flag Question

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

In my script editor I have a code that works perfectly fine, but I am looking to make an update to it. Sometimes when I am working on my data, the lag between when the data comes in is more than one month, and sometimes it is not even a month. Currently I am basing my YTD off of addmonths(Today()-1). Code is below:

InYearToDate(NewDate, addmonths(today(),-1) ,-1) *-1 as CalendarPYTD,

I would like to change it something related to date(max(NewDate)) or

date#(MakeDate($(vBusinessYear),max({<CalendarYear={$(=max(CalendarYear))}>} CalendarMonth),1)). In both cases that would properly return 02 for Feb.

However each time the code fails. Any thoughts on how to best update this code so it is based off of the latest month in the Current Year of my data set?

Thank you in advance!

Justin

Re: YTD Flag Question

sunny_talwar — Wed, 29 Mar 2017 21:03:15 GMT

May be like this

Fact:

LOAD Date,

....

FROM Fact;

MaxDate:

LOAD Max(Date) as MaxDate

Resident Fact;

LET vMaxDate = Peek('MaxDate');

DROP Table MaxDate;

Calendar:

LOAD ...,

InYearToDate(NewDate, addmonths($(vMaxDate), -1) ,-1) *-1 as CalendarPYTD,

Re: YTD Flag Question

Anonymous — Thu, 30 Mar 2017 15:15:25 GMT

Thank you for the help, that works very well.