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

25 Replies
Anonymous
Not applicable
Author

  If(($(vNumMonthYesterday)=1 and Year(Today())=Year(Date)) and (Num(Month(Date))=12 and        Year(Today())=Year(Date)-1), 1,

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

It gives me jan 2015 and jan 2014

rubenmarin

It can be:

  If(($(vNumMonthYesterday)=1 and Year(Today())=Year(Date))-1 and (Num(Month(Date))=12, 1,

      If(Match($(vNumMonthYesterday)-Num(Month(Date)), 0, 1) and Year(Today())=Year(Date)), 1, 0)) as Flag

Anonymous
Not applicable
Author

Sorry, don't understand this error

rubenmarin

some typo with parenthesys:

  If(($(vNumMonthYesterday)=1 and Year(Today())=Year(Date)-1 and (Num(Month(Date))=12, 1,

      If(Match($(vNumMonthYesterday)-Num(Month(Date)), 0, 1) and Year(Today())=Year(Date), 1, 0)) as Flag

Anonymous
Not applicable
Author

Strangely..

It doesn't work

  If($(vNumMonthYesterday)=1 and Year(Today())=Year(Date)-1 and Num(Month(Date))=12, 1,

      If(Match($(vNumMonthYesterday)-Num(Month(Date)), 0, 1) and Year(Today())=Year(Date), 1, 0)) as Flag

rubenmarin

nice uploaded, I don't have time today to make tests and that helps, this expression worked:

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

LOAD Date,

    Sales,

  If($(vNumMonthYesterday)=1 and Year(Today())-1=Year(Date) and Num(Month(Date))=12, 1,

      If(Match($(vNumMonthYesterday)-Num(Month(Date)), 0, 1) and Year(Today())=Year(Date), 1, 0)) as Flag

FROM

[.\2 closed Months.xlsx]

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