Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
rubenmarin

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

25 Replies
MK_QSL
MVP
MVP

Try

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



rubenmarin

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
Not applicable
Author

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
MVP
MVP

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

Anonymous
Not applicable
Author

In attachment..

MK_QSL
MVP
MVP

Do you want Jan 2014 and 2015 both?

Are you interested two month back or one month back?

Anonymous
Not applicable
Author

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

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

Rolling N months

MK_QSL
MVP
MVP

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)