How can I determine if a month-year field is one month ago, two months ago... for each month going back 12 months?

Is there any way to do it without having the longest calculation ever?

Hi Nina,

Is your month-year field a date field (date) or MM-YYYY (as text or number) or ?

Also please explain the purpose of wanting this information and a brief description of the table(s) involved. :^)

or a sample of your file.

In one table I have Sales by Item by Month and Year.

Fields:

• Item
  
• Monthly Sales
  
• Month
  
• Year
  

In another table I have Item Ranking for the past 12 months.

Fields:

• Item
  
• Rank for last month
  
• Rank for 2 months ago
  
• Rank for 3 months ago
  
• Rank for 4 months ago
  
• Rank for 5 months ago
  
• Rank for 6 months ago
  
• Rank for 7 months ago
  
• Rank for 8 months ago
  
• Rank for 9 months ago
  
• Rank for 10 months ago
  
• Rank for 11 months ago
  
• Rank for 12 months ago
  

There is a relationship between the two tables on the Item field.

I need to match the rank up with the correct item, month, and year. I was hoping I could just create a date field: Month &"/1/"& Year, and that there would be a function to subtract 1 month. I was not able to find this function if it does exist and after several attempts yesterday, I finally came up with:

Case (

Month=Case(Month(Get ( CurrentDate ))-1<1;12+Month(Get ( CurrentDate ))-1;Month(Get ( CurrentDate ))-1)

and Year=Case(Month(Get ( CurrentDate ))-1<1;Year(Get ( CurrentDate ))-1;Year(Get ( CurrentDate )));

ItemRank::ItemRankMonth1;

Month=Case(Month(Get ( CurrentDate ))-2<1;12+Month(Get ( CurrentDate ))-2;Month(Get ( CurrentDate ))-2)

and Year=Case(Month(Get ( CurrentDate ))-2<1;Year(Get ( CurrentDate ))-1;Year(Get ( CurrentDate )));

ItemRank::ItemRankMonth2;

Month=Case(Month(Get ( CurrentDate ))-3<1;12+Month(Get ( CurrentDate ))-3;Month(Get ( CurrentDate ))-3)

and Year=Case(Month(Get ( CurrentDate ))-3<1;Year(Get ( CurrentDate ))-1;Year(Get ( CurrentDate )));

ItemRank::ItemRankMonth3;

Month=Case(Month(Get ( CurrentDate ))-4<1;12+Month(Get ( CurrentDate ))-4;Month(Get ( CurrentDate ))-4)

and Year=Case(Month(Get ( CurrentDate ))-4<1;Year(Get ( CurrentDate ))-1;Year(Get ( CurrentDate )));

ItemRank::ItemRankMonth4;

Month=Case(Month(Get ( CurrentDate ))-5<1;12+Month(Get ( CurrentDate ))-5;Month(Get ( CurrentDate ))-5)

and Year=Case(Month(Get ( CurrentDate ))-5<1;Year(Get ( CurrentDate ))-1;Year(Get ( CurrentDate )));

ItemRank::ItemRankMonth5;

Month=Case(Month(Get ( CurrentDate ))-6<1;12+Month(Get ( CurrentDate ))-6;Month(Get ( CurrentDate ))-6)

and Year=Case(Month(Get ( CurrentDate ))-6<1;Year(Get ( CurrentDate ))-1;Year(Get ( CurrentDate )));

ItemRank::ItemRankMonth6;

Month=Case(Month(Get ( CurrentDate ))-7<1;12+Month(Get ( CurrentDate ))-7;Month(Get ( CurrentDate ))-7)

and Year=Case(Month(Get ( CurrentDate ))-7<1;Year(Get ( CurrentDate ))-1;Year(Get ( CurrentDate )));

ItemRank::ItemRankMonth7;

Month=Case(Month(Get ( CurrentDate ))-8<1;12+Month(Get ( CurrentDate ))-8;Month(Get ( CurrentDate ))-8)

and Year=Case(Month(Get ( CurrentDate ))-8<1;Year(Get ( CurrentDate ))-1;Year(Get ( CurrentDate )));

ItemRank::ItemRankMonth8;

Month=Case(Month(Get ( CurrentDate ))-9<1;12+Month(Get ( CurrentDate ))-9;Month(Get ( CurrentDate ))-9)

and Year=Case(Month(Get ( CurrentDate ))-10<1;Year(Get ( CurrentDate ))-1;Year(Get ( CurrentDate )));

ItemRank::ItemRankMonth9;

Month=Case(Month(Get ( CurrentDate ))-10<1;12+Month(Get ( CurrentDate ))-10;Month(Get ( CurrentDate ))-10)

and Year=Case(Month(Get ( CurrentDate ))-10<1;Year(Get ( CurrentDate ))-1;Year(Get ( CurrentDate )));

ItemRank::ItemRankMonth10;

Month=Case(Month(Get ( CurrentDate ))-11<1;12+Month(Get ( CurrentDate ))-11;Month(Get ( CurrentDate ))-11)

and Year=Case(Month(Get ( CurrentDate ))-11<1;Year(Get ( CurrentDate ))-1;Year(Get ( CurrentDate )));

ItemRank::ItemRankMonth11;

Month=Case(Month(Get ( CurrentDate ))-12<1;12+Month(Get ( CurrentDate ))-12;Month(Get ( CurrentDate ))-12)

and Year=Case(Month(Get ( CurrentDate ))-12<1;Year(Get ( CurrentDate ))-1;Year(Get ( CurrentDate )));

ItemRank::ItemRankMonth12)

Which works, but seems extremely long. Is there a better way to do this?

Thank you for any advice.

I don't understand your calculation. Moreover, I don't understand the description of your data: what does "Rank for 8 months ago" mean? Is it updated every month? If so, to get the data of the previous month (relative to the current date), you should always look at "Rank for last month", shouldn't you?

If not, then the "Rank for 8 months ago" will automatically become "Rank for 9 months ago" just by time passing - so that the label is meaningless; we need to know the absolute month (and year) for which the data is correct.

I was hoping I could just create a date field: Month &amp;"/1/"&amp; Year, and that there would be a function to subtract 1 month.

Date ( Month ( monthField ) ; 1 ; Year ( yearField ) )

... Is correct syntax for the first day of the month (result is date). Look up date functions for other examples. You should always use date functions when working with dates. If you use text functions ( an indicator is your section in quotes and the ampersand) then the date will break if you use different OS date setting.

This is for your future information only, Nina - Comment is taking you through the right questions to solve your current issue. :^)

I am importing both the sales and the rank during a nightly import from AS400 via ODBC.

Sales are grouped so I have one record for each item for each month. Year and month are in the original data source as separate fields and are pulled into filemaker that way.

Rank has one record per item with the item's rank for the past 12 months. The month this relates to is relative and changes with time. This is how the data is stored in AS400.

The calculation is checking to see if the sales record is for one month ago to pull and if so pulls the rank from one month ago, then it checks to see if it is two months ago and if so pulls the rank from two months ago...

It looks like using the Date function may help to get me to a simpler calculation.

Rank has one record per item with the item's rank for the past 12 months.

Is it correct to assume you keep only the ranks for the last 12 months? So that if the sales record is for March 2012, and we are now in July, the corresponding rank will be found in the fourth column of the matching record in Ranks? If so, the column name could be calculated and the data fetched using the GetField() function.

Yes, that's correct. That would work and could be a fairly simple calculation.

Thanks a lot