create a blank record

[jtay]

Our Master Calendar projects dates into the next month.

We have a MonthSequence field that numbers each month. 
...

April 2017 is 140

May 2017  is 141

June 2017 is 142.

I need to create a MonthSequence field that ignores next month (June 2017 or 142)  and either puts a null value in there or something less than our beginning MonthSequence number of 77

I tried this (and multiple iterations of it) but I keep getting an expression error:

If((Max(MonthSequence) - (MonthSequence))=0,'', MonthSequence) as TrueMonSeq

Any thoughts on the middle of that if statement... or another way to do this?

thanks
 

[sunny_talwar]

Would you be able to share some data and explain what the desired output based on the sample provided?

[jtay]

I want to create a field that mimics what is in the monthsequence field but does not contain data for next month.

example:

April 1 2017 is 140,

...

April 30, 2017 is 140

May 1, 2017 is 141

...

May 31, 2017 is 141

June 1 and beyond are either blank or less than 77

[jtay · Accepted solution]

So I tried MULTIPLE ways to do a little math on that number without success.

I finally gave up and solved it another way -

here is the script for my MonthSequence field:

,AutoNumber( year(TempDate)&month(TempDate)) as MonthSequence, 

I solved this by just creating another temp table with a different maxDate, running the above line again, and joining to the Master Calendar.