Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
jtanguay
Creator
Creator

YTD Flag Question

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

1 Solution

Accepted Solutions
sunny_talwar

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,

View solution in original post

2 Replies
sunny_talwar

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,

View solution in original post

jtanguay
Creator
Creator
Author

Thank you for the help, that works very well.