Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Bucharest on Sept 18th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
abhaysingh
Specialist II
Specialist II

DateRangeLogic

Hey Guys,

I m trying to make Date Range in My Calendar, but Its Not Working Properly..

Actually i want

if( Date = today(), 'today',

if( Date= today()-1, 'Yesterday',

if( Date>= Weekend(Date) and Date <= WeekStart(Date), 'This Week'))) and so on for 'last 30 Days', 'this Month', 'this Quarter', 'last 6 Month', 'last 12 Month'


Please Advice..


thanks

1 Solution

Accepted Solutions
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

Your excel file doesn't contain dates,  but string values. You first need to turn those into dates using the date# function. Something like:

LOAD *,

  if( Date = today(), 'Today',

       if( Date= today()-1, 'Yesterday',

            if( InWeek(Date,Today(),0), 'This Week',

                 if( InWeek(Date,Today(),-1), 'Last Week',

                      if( InMonth(Date,Today(),0),'This Month',

                           if( InMonth(Date,Today(),-1),'Last Month',

                                if( InQuarter(Date,Today(),0),'This Quarter',

                                     if( InQuarter(Date,Today(),-1),'Last Quarter' )))))))) as Period;

LOAD Date#(Date,'DD/MM/YYYY') as Date,

     Month,

     MonthName,

     Quarter,

     QuarterName,

     Week,

     WeekDay,

     WeekName,

     WeekYear,

     Year,

     Day

FROM [CommunityDateTest.xls] (biff, embedded labels, table is Sheet1$);


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
sujeetsingh
Master III
Master III

you can use the master calender script and just make changes as per your need.

Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

Your excel file doesn't contain dates,  but string values. You first need to turn those into dates using the date# function. Something like:

LOAD *,

  if( Date = today(), 'Today',

       if( Date= today()-1, 'Yesterday',

            if( InWeek(Date,Today(),0), 'This Week',

                 if( InWeek(Date,Today(),-1), 'Last Week',

                      if( InMonth(Date,Today(),0),'This Month',

                           if( InMonth(Date,Today(),-1),'Last Month',

                                if( InQuarter(Date,Today(),0),'This Quarter',

                                     if( InQuarter(Date,Today(),-1),'Last Quarter' )))))))) as Period;

LOAD Date#(Date,'DD/MM/YYYY') as Date,

     Month,

     MonthName,

     Quarter,

     QuarterName,

     Week,

     WeekDay,

     WeekName,

     WeekYear,

     Year,

     Day

FROM [CommunityDateTest.xls] (biff, embedded labels, table is Sheet1$);


talk is cheap, supply exceeds demand
martin_dideriks
Partner - Contributor III
Partner - Contributor III

Gysbert. Are you sure this will work?

If Yesterday is a sunday, this should also be part of "Last week" and probably "This month".

The if-statement will stop evaluating when the first criteria is met, which means that a date only will be part of one period.

Another option is to load the periods one at the time.

Periods:

LOAD

     Date,

     'Last Month' AS Period

RESIDENT MyDateTable

WHERE Date>MonthStart(AddMonths(Today(),-1)) AND Date<MonthStart(Today())

CONCATENATE (Period)

LOAD

........

//Martin

abhaysingh
Specialist II
Specialist II
Author

Hi Gysbert,


could you please suggest me with last 30 days, last 6 month and last 12 month logic in the above condition.