Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

andreyfcdk91
New Contributor III

Closed two month period flag in load script

Good day!

in load script i made flag for last closed month name:

month(Addmonths(Today()-1, -1))

Now i need to make flag (0/1), which will filter data for last closed 2 month period.

Such as:

if(month(Addmonths(Today()-1, -1))='Jan',

here must be Jan (for all years)

,

if(month(Addmonths(Today()-1, -1))='Feb'

,

here must be Jan-Feb (for all years),

if(month(Addmonths(Today()-1, -1))='Mar',

here must be Feb-Mar (for all years)

,

if(month(Addmonths(Today()-1, -1))='Apr',

here must be Mar-Apr (for all years)

,

if(month(Addmonths(Today()-1, -1))='May',

here must be Apr-May  (for all years)

,

if(month(Addmonths(Today()-1, -1))='Jun',

here must be May-Jun  (for all years)

,

if(month(Addmonths(Today()-1, -1))='Jul',

here must be Jun-Jul  (for all years)

,

if(month(Addmonths(Today()-1, -1))='Aug',

here must be Jul-Aug  (for all years)

,

if(month(Addmonths(Today()-1, -1))='Sep',

here must be Aug-Sep  (for all years)

,

if(month(Addmonths(Today()-1, -1))='Oct',

here must be Sep-Oct  (for all years)

,

if(month(Addmonths(Today()-1, -1))='Nov',

here must be Oct-Nov  (for all years)

,

if(month(Addmonths(Today()-1, -1))='Dec',

here must be Nov-Dec  (for all years)

))))))))))))

Please, help!)

1 Solution

Accepted Solutions

Re: Closed two month period flag in load script

Not sure about what you want, it's based on current date?, so if today is 16 december you want to flag all october and november dates?

In that case this script seems work:

Let vNumMonthYesterday = Num(Month(AddMonths(Today()-1, -1)));

LOAD Date,

     Sales,

     If(Match($(vNumMonthYesterday)-Num(Month(Date)), 0, 1), 1, 0) as Flag

FROM

[.\2 closed Months.xlsx]

(ooxml, embedded labels, table is Лист1);

Also it's supposed that only flags Jan when Jan is last closed month but I didn't tested that.

25 Replies

Re: Closed two month period flag in load script

Try

If(Month(AddMonths(Today(),-2)) = 'Jan') for 2 months before and -1 for one month before



Re: Closed two month period flag in load script

Hi, this post can help you: The As-Of Table

Check the example with the x-month rolling , I think you're asking about 2-months rolling, logic is the same.

andreyfcdk91
New Contributor III

Re: Closed two month period flag in load script

Thanks.

but i need following flag, for exaple, if last closed month is Jan

if(month(Addmonths(Today()-1, -1))='Jan',

if(condition to get 'Jan' for all years, 1, 0)

....

Re: Closed two month period flag in load script

Provide some sample data along with expected output in excel file..

andreyfcdk91
New Contributor III

Re: Closed two month period flag in load script

In attachment..

Re: Closed two month period flag in load script

Do you want Jan 2014 and 2015 both?

Are you interested two month back or one month back?

andreyfcdk91
New Contributor III

Re: Closed two month period flag in load script

1. Yes, for two years. But this is test data, in my real model there're 2015, 2014, 2013 ... Jan for all years

2. One month back only for Jan (if(month(Addmonths(Today()-1, -1))='Jan'). For all others - two month back.

Thanks.

Re: Closed two month period flag in load script

see the below link , this will help to create the flags

Rolling N months

Re: Closed two month period flag in load script

Data:

Load

  *,

  Month(Date) as Month,

  If(Month(Date) = Month(AddMonths(Today(),-2)),1,0) as Temp_Flag

Inline

[

  Date, Sales

  01.01.2015, 5454

  02.01.2015, 54

  01.01.2015, 234

  01.01.2014, 634

  02.01.2014, 23

  01.01.2014, 64

  01.02.2015, 5454

  02.02.2015, 54

  01.02.2015, 234

  01.02.2014, 634

  02.02.2014, 23

  01.02.2014, 64

  01.03.2015, 100

  02.03.2015, 110

  01.02.2015, 50

  01.03.2014, 40

  02.03.2014, 60

  01.03.2014, 65

  01.04.2015, 44

  02.04.2015, 43

  01.04.2015, 20

  01.04.2014, 10

  02.04.2014, 5

  01.04.2014, 25

];

Final:

Load Date,Month, Sales, If(RowNo()=1,Temp_Flag, If(Temp_Flag=1 and Previous(Temp_Flag)=0, Temp_Flag, Peek('Flag'))) as Flag Resident Data Order By Month Desc, Date Desc;

Drop Table Data;

Now you can use

=SUM({<Flag = {1}>}Sales)

Community Browser