13 Replies Latest reply: Jan 24, 2018 7:06 AM by Sunny Talwar

# Master calendar month number issue

Hi all,

I have a master calendar ranging from Jan-2017 to December-2018. I have generated month number for all the 24 months. The problem i am facing is that Max(MonthNumber) gives me Dec-18, but i want to point at the current month that is Jan-18.

Is there a way i can identify the month number of current month which i can use in set analysis?

Thanks!

• ###### Re: Master calendar month number issue

May be like this

Date(MonthStart(Today()), 'MMM-YY')

• ###### Re: Master calendar month number issue

I am using the same thing for current month, and for previous months i am just subtracting from it and getting the required months. But consider the following scenario:

I need to know the

1. sum(sales) - Current month

2. sum(sales) - Previous month

3. sum(sales) - All the rest of the previous months.

The third point is where i am facing problem

• ###### Re: Master calendar month number issue

May be like this

Sum({<MonthYearField= {"\$(='<=' & Date(MonthStart(Today()), 'MMM-YY'))"}>}Sales)

• ###### Re: Master calendar month number issue

Thank u so much Sunny. Its working just fine!

• ###### Re: Master calendar month number issue

Is it possible to select month ranges here?

Like 0-4 months,5-6 months, where

0-Jan-18

1-Dec-17

2-Nov-17 and so on?

• ###### Re: Master calendar month number issue

Yes, it should be... but select month ranges within the expression or for selections?

• ###### Re: Master calendar month number issue

Within expressions. Actually i have done something and its working.

Any alternate/efficient way would be appreciated!

• ###### Re: Master calendar month number issue

What have you done? May be we can make changes to what you already have? Will also give me an idea of what you might be looking for

• ###### Re: Master calendar month number issue

Also, once you have what you needed, please close the thread by marking correct and helpful responses.

Best,

Sunny

• ###### Re: Master calendar month number issue

Hi Arpit,

In all my reports with dates fields, I create indexes such as a master field called Month_index using below:

= (

Num( Year(Date) ) * 12 + Num( Month(Date) )

) // Creates month number for each number

-

(

Num( Year(Now()) ) * 12 + Num( Month(Now()) )

)// Creates month number for this month

This counts backwards from current month

0 = current month

-1 = last month

-2 = previous month

etc

means you can then use simple set analysis such as

sum(  Sales {\$<Month_index  = '0'>}) //current month

sum(  Sales {\$<Month_index  = '-1'>}) // previous month

sum(  Sales {\$<Month_index  = { '<-1'}>}) // All other previous months

Same can be applied to weeks and years etc...

Thanks

D

:-)

• ###### Re: Master calendar month number issue

Thanks for the reply Darrell. This one may also work fine!

• ###### Re: Master calendar month number issue

You could try this..

num(     month(<Date>)     )     ) // will return 1 for Jan 18

=Year (<Date>) *100+ num(month(<Date>)) // willl return 201801

Thanks D

:-)

• ###### Re: Master calendar month number issue

If you want current then use now() instead of <date>

You could try this..

num(     month(now())     )     ) // will return 1 for Jan 18

=Year (now()) *100+ num(month(now())) // willl return 201801

Thanks D

:-)