Looking back 6 months using a Load Date and Start Date

[#1]

Hello:

I am having a tough time with a calculation.  I do not have a calendar in my Dashboard, but the data I am using has dates.  I am looking to use a field called 'Load Date' and look to see how many items were created in the past 6 months.  I would be using another field named 'Issue Start Date'. I would need a count of those items that were started within 6 months of the Load Date.  Is this possible?

Thank you for your time.

[tchovanec]

Are you looking for items within 6 months prior and 6 months after the load date?

[#3]

Hi Thomas,

I am only looking for the past 6 months worth of data.  Current to 6 months prior only.

[parimikittu]

Hi, Please use the below code and let me know.

"count({}items) "

[#5]

Hi ,

May be like this

count({$<LoadDate={">=$(=MonthStart(AddMonths(Today(),-6)))<=$(=MonthEnd(AddMonths(Today())))"}>} Items)

[#6]

Hi Partha:

Thank you for your response.  Is it possible to get a distinct count using this formula?  I have duplicates in the source data.  My formula is as follows:

count(Distinct({$<[Load Date]={">=$(=MonthStart(AddMonths(Today(),-6)))<=$(=MonthEnd(AddMonths(Today())))"}>} [Issue ID]))

The formula without the Distinct works well.

[#7]

I have tried the following formula and it doesn't work.

count(Distinct({$<[Load Date]={">=$(=MonthStart(AddMonths(Today(),-6)))<=$(=MonthEnd(AddMonths(Today())))"}>} [Issue ID]))

Any help would be greatly appreciated!

Thank you

[#8 · Accepted solution]

The Calculation should be :

count(Distinct{$<[Load Date]={">=$(=MonthStart(AddMonths(Today(),-6)))<=$(=MonthEnd(AddMonths(Today())))"}>} [Issue ID])

I had to remove the ( from after Distinct and the ) from the end.