Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

stuhico09
Contributor

How should I create new field in load script (select last 12 month date)?

Dear Team,

I want to create new field in load script using if else.

For example field "Last12Month", it will only select last 12 month period exclude this month.

I want to create few field:

a) Last12Month

b) Last24Month

c) Last36Month.

Please help.

Thanks.

period script.PNG

period table.PNG

1 Solution

Accepted Solutions
stuhico09
Contributor

Re: How should I create new field in load script (select last 12 month date)?

Dear Massimo,

Thanks. I do it like this.

    if(Date(date#(Period,'YYYYMMDD'),'DD-MMM-YYYY')> AddMonths(MonthStart(Today()),-35),(Date(date#(Period,'YYYYMMDD'),'DD-MMM-YYYY')))

   

    as Last36Month,

   

      if(Date(date#(Period,'YYYYMMDD'),'DD-MMM-YYYY')> AddMonths(MonthStart(Today()),-23),(Date(date#(Period,'YYYYMMDD'),'DD-MMM-YYYY')))

   

    as Last24Month,

   

      if(Date(date#(Period,'YYYYMMDD'),'DD-MMM-YYYY')> AddMonths(MonthStart(Today()),-11),(Date(date#(Period,'YYYYMMDD'),'DD-MMM-YYYY')))

   

    as Last12Month,

   

      if(Date(date#(Period,'YYYYMMDD'),'DD-MMM-YYYY')> AddMonths(MonthStart(Today()),-5),(Date(date#(Period,'YYYYMMDD'),'DD-MMM-YYYY')))

   

    as Last6Month,

2 Replies
MVP
MVP

Re: How should I create new field in load script (select last 12 month date)?

An option could be

https://help.qlik.com/en-US/sense/September2017/Subsystems/Hub/Content/Scripting/DateAndTimeFunction...

// last 12

InYear(Date#(Period, 'YYYYMMDD'), AddMonths(MonthStart(today()), -1), 0, Month(today())) as Last12Month

// last 24

InYear(Date#(Period, 'YYYYMMDD'), AddMonths(MonthStart(today()), -1), 0, 5) or InYear(d, AddMonths(MonthStart(today()), -1), -1, 5) as Last24Month


stuhico09
Contributor

Re: How should I create new field in load script (select last 12 month date)?

Dear Massimo,

Thanks. I do it like this.

    if(Date(date#(Period,'YYYYMMDD'),'DD-MMM-YYYY')> AddMonths(MonthStart(Today()),-35),(Date(date#(Period,'YYYYMMDD'),'DD-MMM-YYYY')))

   

    as Last36Month,

   

      if(Date(date#(Period,'YYYYMMDD'),'DD-MMM-YYYY')> AddMonths(MonthStart(Today()),-23),(Date(date#(Period,'YYYYMMDD'),'DD-MMM-YYYY')))

   

    as Last24Month,

   

      if(Date(date#(Period,'YYYYMMDD'),'DD-MMM-YYYY')> AddMonths(MonthStart(Today()),-11),(Date(date#(Period,'YYYYMMDD'),'DD-MMM-YYYY')))

   

    as Last12Month,

   

      if(Date(date#(Period,'YYYYMMDD'),'DD-MMM-YYYY')> AddMonths(MonthStart(Today()),-5),(Date(date#(Period,'YYYYMMDD'),'DD-MMM-YYYY')))

   

    as Last6Month,