rolling 13 months

[Pomna]

Hello everyone,

Please I need help creating rolling 13 months on a bar chart. The code below works well for last month's metrics but I need to do rolling 13 months. I need to get remaining inventory for each month for the last 13 months excluding current month.

The code below gives me a count of distinct issue ID where created date is less than current month and status not equal to done or closed plus distinct issue ID where created date is less than current month  and closed date  is >= monthstart today. 

Count({$<[Date Created]={'<$(=MonthStart(Today()))'},
[Status Name]=-{'Closed','DONE'}> +
<[Date Created]={'<$(=MonthStart(Today()))'},
[Date Closed] = {'>=$(=MonthStart(Today()))'}> }DISTINCT [Issue ID])

[JordyWegman]

Hi,

Try this:

Count({$<[Date Created]={'<$(=MonthStart(Today()))'},
[Status Name]=-{'Closed','DONE'}> +
<[Date Created]={'<$(=MonthStart(Today()))'},
[Date Closed] = {">=$(=AddMonths(MonthStart(Today()),-13))"}> }DISTINCT [Issue ID])

This will get all dates that are closed from 13 months ago till now.

Jordy

Climber 

[Pomna]

Thanks for the response Jordy but it does not solve my problem. I get more months and dates from 2013 and the numbers    are way higher than they should be. The code without your addition gives me the numbers I expect but am at a loss on how to get 13 months rolling.

[JordyWegman]

I don't really get the problem now. How can your current figures be good if you don't have a 'rolling 13 months' in your code. What is it that you need? Do you maybe have a test set that we can use?

Jordy

Climber

[Pomna]

The code gives me the right number for last month but I would like to have it 13 months rolling. My problem is getting it work for the last 13 months