Closed two month period flag in load script

[Anonymous]

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 · Accepted solution]

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.

[Anonymous]

in your solution flag has only one value: 0...

[rubenmarin]

If shows only 0 is because you don't have data for october or november, or because the date format is different than from the Excel, if you add data to november and october you can see the '1'.

[Anonymous]

Thanks!

Will it works for others condition? For example,  if last closed month is Jan, it must filter data for Jan, if last closed month in Feb: Jan-Feb?

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)

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

[rubenmarin]

It should work but I don't have time now to test, change "Today()" (in the variable) to the date you want to test to confirm it's working in all cases.

[Anonymous]

Thanks!!! it works!

Dear, tell me please, is there in your expression possibility to make change for closed month Jan: get Jan'Current year-Dec'Previous Year?

Thanks.

[rubenmarin]

You can add a condition to the 'If' to check if it's january and date is december:

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

LOAD Date,

     Sales,

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

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

FROM

[.\2 closed Months.xlsx]

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

[Anonymous]

Thanks!

i've try it.

it gives me also jan for 2014. But i need jan only forcurrent year and dec for previous.

[rubenmarin]

You can modify the 'If' to fit your requirements checking conditions for Year(Today())=Year(Date) or Year(Today())=Year(Date)-1

[Anonymous]

please, can you write example?

Thanks