Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Creating Last 6/12/18/24 Months

Guys,

I have this field [Claim Close Date] that is my date field, set day-by-day. I want to create a field that gets the last 6 months, for example: we are in april/2014, i want to get all days from october/2013 - march/2014 ; I want that for last 12 months (march/2013 ~ march/2014), and I want the same for the last 18 months and the last 24 months.

Can you guys script for me a way out of that?

1 Solution

Accepted Solutions
MVP
MVP

Re: Creating Last 6/12/18/24 Months

if I understand

you can add some flag in a tabel associated to your date

LET Start = num(makedate(2012,1,1));

LET End = num(makedate(2014,12,1));

LET NumOfDays = End - Start + 1;


Date_src:

LOAD

$(Start) + Rowno() -1 as DateId

AUTOGENERATE $(NumOfDays);

ClaimCloseDateFlag:

LOAD

DateId as [Claim Close Date],

if(DateId >= AddMonths(MonthStart(today()), -6) and DateId <= monthstart(today())-1, 1, 0) as Flag6Month

// similar for Flag12,,,,,,,18.....2$

RESIDENT Date_src;

Drop Table Date_src;

3 Replies
yduval75
Contributor III

Re: Creating Last 6/12/18/24 Months

I think you have to create two fields : start date and end date.
Then, you have to use the interval match function

MVP
MVP

Re: Creating Last 6/12/18/24 Months

if I understand

you can add some flag in a tabel associated to your date

LET Start = num(makedate(2012,1,1));

LET End = num(makedate(2014,12,1));

LET NumOfDays = End - Start + 1;


Date_src:

LOAD

$(Start) + Rowno() -1 as DateId

AUTOGENERATE $(NumOfDays);

ClaimCloseDateFlag:

LOAD

DateId as [Claim Close Date],

if(DateId >= AddMonths(MonthStart(today()), -6) and DateId <= monthstart(today())-1, 1, 0) as Flag6Month

// similar for Flag12,,,,,,,18.....2$

RESIDENT Date_src;

Drop Table Date_src;

Not applicable

Re: Creating Last 6/12/18/24 Months

This works just perfect, thank you. We use alot the numbers from last 6 months and 12 months. Thank you very much!