Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!)
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
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
Sorry, don't understand this error
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
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
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);