sccardais

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.

Outcomes