AnsweredAssumed Answered

Sum each item over the last 3 months

Question asked by clo mama on May 9, 2018
Latest reply on May 13, 2018 by bigtom

Hi,

 

In Filemaker Pro 12, I am trying to write a formula for a calculation field that will sum each product item being sold over the last 3 months. Take a product item "YYYY" as an example. On 1/3/2018, I need to sum the total of the product being sold between 31/12/2017 and 28/2/2018. On 1/4/2018, sum the total between 31/1/2018 and 31/3/2018.....

 

Here are the fields in the table:

ProductCategoryProductItemProductValue_Sold
Date_MthEnd
A00AXXX46731/3/2018
A00AXXX13730/4/2018
A00AYYY31031/12/2017
A00AYYY19831/1/2018
A00AYYY22928/2/2018
A00AYYY55231/3/2018
A00AYYY31130/4/2018
A00AZZZ9831/1/2018
A00AZZZ9228/2/2018
A00AZZZ8331/3/2018
B99CPPPP313531/12/2017
B99CPPPP406431/1/2018
B99CPPPP538128/2/2018
B99CPPPP920731/3/2018
B99CPPPP835030/4/2018
B99CQQQQ147631/1/2018
B99CQQQQ201228/2/2018
B99CQQQQ300931/3/2018
B99CQQQQ111230/4/2018

 

 

I created a self-join relationship and selected "ProductCategory" & "ProductItem" as the match fields for both sides of the relationship. Also, I created a new calculation field with the equation as Case ( Date_MthEnd = Get ( CurrentDate ) - 3 ; Sum ( MthEndValue::ProductValue_Sold ); "" ). However, the field is empty.

 

 

Any advice would be eternally grateful.

 

 

Thanks!

 

 

Julis

Outcomes