Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Rolling Calendar and IF Statement

I have a rolling 13 month calendar that is used for several clients. However one client's data only starts in Feb 2017.

I need an if statement so any months prior to Feb 2017 will not show (If RollingStartDate is < Feb 2017 ???.

How do I add that to the below?

Let vYear = Year(Today()-1);

Let vPriorYear = Year(Today()-1)-1;

Let vRollingStartDate = Date(MonthStart(AddMonths(Today()-1, -12)));

Let vToday = Date(Today()-1);

Thanks for the assist - Jim

1 Solution

Accepted Solutions
felipedl
Partner - Specialist III
Partner - Specialist III

Let vYear = Year(Today()-1);

Let vPriorYear = Year(Today()-1)-1;

if (Date(MonthStart(AddMonths(Today()-1, -12))) < 42767) then

     Let vRollingStartDate = 42767;

else

     Let vRollingStartDate = Date(MonthStart(AddMonths(Today()-1, -12)));

end if;

Let vToday = Date(Today()-1);

In this logic, if twelve months back is lesser then feb 2017, it wil start from feb on, and if not, it will get 12 months back.

Felipe.

View solution in original post

4 Replies
felipedl
Partner - Specialist III
Partner - Specialist III

Hi James,

If you have the RollingStartDate in the standard excel format and have created the date in View, the if statement can be as follows:


if (RollingStartDate < 42767,... // 42767 is the equivalent of 02/01/2017 or February 1st in Excel.


Felipe.

Not applicable
Author

Hi Felipe

Thank you

How would I add that to the code I gave? (New to Qlik Sense)

Eventually the 13 month calendar would kick in.

felipedl
Partner - Specialist III
Partner - Specialist III

Let vYear = Year(Today()-1);

Let vPriorYear = Year(Today()-1)-1;

if (Date(MonthStart(AddMonths(Today()-1, -12))) < 42767) then

     Let vRollingStartDate = 42767;

else

     Let vRollingStartDate = Date(MonthStart(AddMonths(Today()-1, -12)));

end if;

Let vToday = Date(Today()-1);

In this logic, if twelve months back is lesser then feb 2017, it wil start from feb on, and if not, it will get 12 months back.

Felipe.

Not applicable
Author

This is fantastic - just what I needed to help me understand

Thank you - it has been marked as correct.