topic Re: DateRangeLogic in QlikView

DateRangeLogic

abhaysingh — Tue, 06 Oct 2015 05:18:28 GMT

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

Re: DateRangeLogic

sujeetsingh — Tue, 06 Oct 2015 05:49:34 GMT

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

Re: DateRangeLogic

Gysbert_Wassenaar — Tue, 06 Oct 2015 05:55:31 GMT

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$);

Re: DateRangeLogic

martin_dideriks — Tue, 06 Oct 2015 06:41:30 GMT

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

Re: DateRangeLogic

abhaysingh — Tue, 06 Oct 2015 10:00:11 GMT

Hi Gysbert,

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