Previous month value

[ashis]

Hello,

I have NumMonth field in my master calendar, and I am using the following code to generate NumMonth.

num(Month(TempDate)) As NumMonth

Now I want to get previous months count m based on my month selection. I am using this code.

=count({$< NumMonth={"=num(max(NumMonth))-1"}>} Reconciliation_ID)

This code is giving me current months count. if I hard code the value as

=count({$< NumMonth={5}>} Reconciliation_ID)  I get the result . however it is not working with the code I am using.

Please help

Thank you,

Ashis

[ashis]

Hi Sunny,

Thanks for your reply. That is very valid point. It leads me to think I should not use numeric value for month.

I tried the following code also

=count({<Month={"$(=Date(AddMonths(max(Month),-1),'MMM'))"}>} Reconciliation_ID)

I am facing one serious issue , please see my below code for master calendar.

For month calculation if I use  Month(TempDate) As Month , I find it difficult to calculate such as month , previous month .

however if I use Date(MonthStart(TempDate),'MMM') as Month  ,  I find it give correct result in set analysis.

in my current scenario that I am working on , following code Date(MonthStart(TempDate),'MMM') as Month  is giving me duplicate month values such as Aug twice(since I have two years and in both the years I have few common months) , in my list box which I do not want.

I will be forever grateful if you let me know which formula to follow in master calendar.

--------------------------------------------------------

QuartersMap: 
MAPPING LOAD  
rowno() as Month, 
'Q' & Ceil (rowno()/3) as Quarter 
AUTOGENERATE (12); 

Temp: 
Load 
min(Date) as minDate, 
max(Date) as maxDate 
Resident ExceptionDataFeedRun; 

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 
DROP Table Temp; 

TempCalendar: 
LOAD 
$(varMinDate) + Iterno()-1 As Num, 
Date($(varMinDate) + IterNo() - 1) as TempDate 
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

asterCalendar: 
Load 
TempDate AS [Date],
week(TempDate) As Week, 
Year(TempDate) As Year, 
Month(TempDate) As Month,
Date(MonthStart(TempDate),'MMM-YY') as YearMonth,
//Date(MonthStart(TempDate),'MMM') as Month,
  num(Month(TempDate)) As NumMonth,
Day(TempDate) As Day, 
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 
WeekDay(TempDate) as WeekDay 
Resident TempCalendar 
Order By TempDate ASC; 
Drop Table TempCalendar; 

[sunny_talwar]

If that works for you, go for it... but I would probably use YearMonth field from your calendar to do this

Date(MonthStart(TempDate),'MMM-YY') as YearMonth,

Something like this

=Count({$<YearMonth = {"$(=Date(MonthStart(Max(YearMonth), -1), 'MMM-YY'))"}, NumMonth, Week, Year, Month, Date, Day, Quarter, WeekYear, WeekDay>} Reconciliation_ID)

[ashis]

Hi Brice,

Thank you for your reply. I added your code in my script, please see the attach code.

How ever id did not work,

=count({$< MonthCounter={'$(=MAX(MonthCounter) - 1)'}>} Reconciliation_ID)

Master calendar:

-------------------

QuartersMap: 
MAPPING LOAD  
rowno() as Month, 
'Q' & Ceil (rowno()/3) as Quarter 
AUTOGENERATE (12); 

Temp: 
Load 
min(Date) as minDate, 
max(Date) as maxDate 
Resident ExceptionDataFeedRun; 

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 
DROP Table Temp; 

TempCalendar: 
LOAD 
$(varMinDate) + Iterno()-1 As Num, 
Date($(varMinDate) + IterNo() - 1) as TempDate 
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

MasterCalendar: 
Load 
TempDate AS [Date],
week(TempDate) As Week, 
Year(TempDate) As Year, 
Month(TempDate) As Month,
Date(MonthStart(TempDate),'MMM-YY') as YearMonth,
//Date(MonthStart(TempDate),'MMM') as Month,
  Year(TempDate) * 12 + Month(TempDate) as [MonthCounter],
num(Month(TempDate)) As NumMonth,
Day(TempDate) As Day, 
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 
WeekDay(TempDate) as WeekDay 
Resident TempCalendar 
Order By TempDate ASC; 
Drop Table TempCalendar; 

[Brice-SACCUCCI]

I've forgotten a ')', sorry! I've edited my first answer

[ashis]

Hi Brice,

Please see my code, I have added your code in my Master Calender. then I used [Month Counter] in my set analysis

=count({$< [Month Counter]={'$(=MAX([Month Counter] - 1))'}>} Reconciliation_ID)  or

=count({$< [Month Counter]={'$(=MAX([Month Counter]-1))'}>} Reconciliation_ID) , however it is returning 0 value. Please guide.

MasterCalendar: 
Load 
TempDate AS [Date],
week(TempDate) As Week, 
Year(TempDate) As Year, 
Month(TempDate) As Month,
Date(MonthStart(TempDate),'MMM-YY') as YearMonth,
//Date(MonthStart(TempDate),'MMM') as Month,
  Year(TempDate) * 12 + Month(TempDate) as [Month Counter],
num(Month(TempDate)) As NumMonth,
Day(TempDate) As Day, 
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 
WeekDay(TempDate) as WeekDay 
Resident TempCalendar 
Order By TempDate ASC; 

[bhadraka1234]

Count({$<NumMonth= {"<=$(=max(NumMonth))    >$(=max(NumMonth)-(5))"}>}DISTINCT NumMonth)

[ashis]

Hi Brice,

Thank you for your guidance, it worked for me if I am calculating using variable.like the following script

vPreviousMonthCounter=max([Month Counter]) -1

=count({1<[Month Counter]={'$(vPreviousMonthCounter)'}>} Reconciliation_ID) 

Thank you,