topic Re: Need a logic for date field in QlikView

Need a logic for date field

chinnu123 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Need a logic for date field

sergio0592 — Mon, 10 Jul 2017 18:42:57 GMT

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

Re: Need a logic for date field

chinnu123 — Tue, 11 Jul 2017 08:06:32 GMT

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

Thanks,

Bhargav.

Re: Need a logic for date field

jmvilaplanap — Tue, 11 Jul 2017 08:20:43 GMT

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

Re: Need a logic for date field

sergio0592 — Tue, 11 Jul 2017 08:22:28 GMT

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

Re: Need a logic for date field

ahaahaaha — Tue, 11 Jul 2017 08:37:45 GMT

Hi,

I think this is what you need

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

Regards,

Andrey

Re: Need a logic for date field

tresB — Tue, 11 Jul 2017 09:07:13 GMT

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

Re: Need a logic for date field

chinnu123 — Tue, 11 Jul 2017 09:24:04 GMT

HI jean,

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

Thanks,

Bhargav.

Re: Need a logic for date field

Kushal_Chawda — Tue, 11 Jul 2017 09:25:09 GMT

see this

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

Re: Need a logic for date field

chinnu123 — Tue, 11 Jul 2017 10:03:14 GMT

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.

Re: Need a logic for date field

chinnu123 — Tue, 11 Jul 2017 10:16:35 GMT

Hi Tresco,

Thanks for your Help. Can you please provide me the for previous week previous month previous year also.

Re: Need a logic for date field

chinnu123 — Wed, 12 Jul 2017 10:53:40 GMT

Hi ,

PFA !

Re: Need a logic for date field

arvind_patil — Wed, 12 Jul 2017 11:10:00 GMT

Hi Chinnu,

You can use below script for this:

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load

min(TransactionDate) as minDate,

max(TransactionDate) as maxDate

Resident LinKTable;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

Let CurrentDate=Num(Peek('maxDate', 0, 'Temp'));

Let CurrentYear=Year(Num(Peek('maxDate', 0, 'Temp')));

DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load

TempDate AS TransactionDate,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

YeartoDate(TempDate)*-1 as CurYTDFlag,

YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Thanks,

Arvind Patil