topic Re: Creating Last 6/12/18/24 Months in QlikView

Creating Last 6/12/18/24 Months

Tue, 08 Apr 2014 14:09:30 GMT

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?

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

yduval75 — Tue, 08 Apr 2014 14:14:45 GMT

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

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

maxgro — Tue, 08 Apr 2014 16:45:14 GMT

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;

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

Tue, 08 Apr 2014 17:45:30 GMT

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