Calculate number of months in related records

Discussion created by sccardais on May 21, 2016
Latest reply on May 31, 2016 by sccardais

I'm creating a report based on a rolling 12 months of data. I don't want to summarize data if there isn't at least 12 months of data.


My data table contains 4,000 records with fields containing a transaction Date and Value and a calculation field that shows the date 12 months prior to the Date field.


A self join relationship finds all of the records within the previous 12 months of the Date.


I'd like to know how many months of related data I have.


For example, assuming my transaction date is 5/1/2016, the start date for my calculation would be 5/1/2015. Assuming I have transactions in each month between these two dates, the result would be 12. If one month had no transactions, the result would be 11.


I'm at a loss as to how to create this calculation.