Need a logic for date field

[chinnu123]

Hi team,

I have a date field called open date by using this I need to derive the below fields.

Today

Yesterday

Current week this week from Monday

Last week means last week Monday to Friday data

Current month means this month start to till date data

Last month means jun 1st to June 30 data

Current quarter

Last quarter means April to June data

Current year means Jan 2017 to till now

Last year means Jan 2016 to december data

And after deriving above fields I need to take these all derived fields in single list box so user can select accordingly what he need.

Hope I was clear. Can any one please help on this.

  Thanks in advance

Thanks,

Chinnu.

[sergio0592]

Hi,

In a load statement, add the following. I've tried, it works.

Today : today()

Yesterday : date(today()-1,'DD/MM/YYYY')

Current week this week from Monday : weekname(date_field)

Last week means last week Monday to Friday data : weekname(WeekStart(date_field),-1)

Current month means this month start to till date data : month(date_field)

Last month means jun 1st to June 30 data : month(date_field)-1

Current quarter: QuarterName(date_field)

Last quarter means April to June data : QuarterName(QuarterStart(date_field),-1)

Current year means Jan 2017 to till now : Year(date_field)

Last year : Year(SubExpDate)-1

[chinnu123]

But how can I take all the above fiels in Single list box?

Thanks,

Bhargav.

[jmvilaplanap]

Hi

I always use this calendar script:

Calendar:
LOAD
floor(num(TempDate))                      as DateNum,
Date(TempDate, 'DD/MM/YYYY')              as Date,
year(TempDate)                                  as Year,
year(TempDate)& ' ' & month(TempDate)     as YearMonth,
num(month(TempDate),00)                        as MonthNumber,
month(TempDate)                                 as MonthName,
month(TempDate)                                 as Month,
day(TempDate)                                   as Day,
WeekDay(TempDate)                               as WeekDay,
WeekName(TempDate)                             as WeekName,

if (month(TempDate) > 9, year(TempDate) & ' Q4',
if (month(TempDate) > 6, year(TempDate) & ' Q3',
if (month(TempDate) > 3, year(TempDate) & ' Q2',
year(TempDate) & ' Q1'))) as Quarter,
year(TempDate)& num(month(TempDate),00)   as YearMonthNum;
LOAD
minDate + IterNo() - 1 AS TempDate
While minDate + IterNo() - 1 <= maxDate;

LOAD
min(FieldValue('DateNum', recno())) as minDate,
max(FieldValue('DateNum', recno())) as maxDate
AutoGenerate FieldValueCount('DateNum');

DesvCalendar:
LOAD
DateNum         AS H_DateNumDesv,
Year       AS H_YearDesv,
Quarter         AS H_QuarterDesv
Resident Calendar;

You can adapt it to your specifications and use the part of script that sergio0592 said.

Normally to calculate month to day, or year to day I use a set analysis expression directly in the object.

Regards

[sergio0592]

You have just to use the created  fields in list boxs.

[ahaahaaha]

Hi,

I think this is what you need

https://www.resultdata.com/qlik-custom-calendars/‌

Regards,

Andrey

[tresesco]

May be like this?

Load

       *,

       Year(Date) as Year,

       Month(Date) as Month;

Load

       Date(Today()-365*3+RecNo()) as Date

AutoGenerate 365*3;

listbox:

Load

       Today() as Date,

       'Today' as Common

AutoGenerate 1;

Load

       Date(Today()-1) as Date,

       'Yesterday' as Common

AutoGenerate 1;

Load

       Date(WeekStart(Today())+RecNo()) as Date,

       'Current Week' as Common

AutoGenerate Today()-WeekStart(Today());

Load

       Date(MonthStart(Today())+RecNo()) as Date,

       'Current Month' as Common

AutoGenerate Today()-MonthStart(Today());

[chinnu123]

HI jean,

Thanks for your reply, But I need all the fields in One list box.

Thanks,

Bhargav.

[Kushal_Chawda]

see this

A Rapid "Fast Calendar Selections...", a great boost of multi periods analysis! ...

[chinnu123]

Thanks Tresco for your reply,

This is what I am looking to achieve and can you please suggest for current quarter and last quarter also

Thanks,

Chinnu.