Solved: Closed two month period flag in load script - Qlik Community

Anonymous · ‎2015-12-16

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!)

rubenmarin · ‎2015-12-16

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.

View solution in original post

MK_QSL · ‎2015-12-16

Try

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

rubenmarin · ‎2015-12-16

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.

Anonymous · ‎2015-12-16

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)

....

MK_QSL · ‎2015-12-16

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

Anonymous · ‎2015-12-16

In attachment..

MK_QSL · ‎2015-12-16

Do you want Jan 2014 and 2015 both?

Are you interested two month back or one month back?

Anonymous · ‎2015-12-16

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.

Kushal_Chawda · ‎2015-12-16

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

Rolling N months

MK_QSL · ‎2015-12-16

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)