Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean:
Creator III

## Previous month value

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.

Thank you,

Ashis

16 Replies
Creator III
Author

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

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:
rowno() as Month
'Q' &
Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);

Temp:
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:
\$(varMinDate) + Iterno()-1 As Num
Date(\$(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While \$(varMinDate) + IterNo() -1 <= \$(varMaxDate)

asterCalendar:
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;

MVP

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)

Creator III
Author

Hi Brice,

How ever id did not work,

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

Master calendar:

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

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

Temp:
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:
\$(varMinDate) + Iterno()-1 As Num
Date(\$(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While \$(varMinDate) + IterNo() -1 <= \$(varMaxDate)

MasterCalendar:
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;

Employee

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

Creator III
Author

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

Contributor II

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

Creator III
Author

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,

Community Browser