Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 >= month start today.
Count({$<[Date Created]={'<$(=Month Start(Today()))'},
[Status Name]=-{'Closed','DONE'}> +
<[Date Created]={'<$(=MonthStart(Today()))'},
[Date Closed] = {'>=$(=MonthStart(Today()))'}> }DISTINCT [Issue ID])
Create a Calendar including a flag for each month with the Months from Current Month
I used this function:
SET MonthDiff = Num(((year($2) * 12) + month($2)) - (((year($1) * 12) + month($1)))+1 );
Then added to calendar as a field:
($(MonthDiff("Date",Today(1)))) AS MonthsToDate,
(look for canonical date or calendar script for creating a master calendar)
Thanks DWforest for the response. I have a canonical calendar and a flag already but am at a loss on how to make that work. Below is my code but it does not work
Count({$<[Date Created]={'<$(=MonthStart(Today()))'}, [Status Name]=-{'Closed','DONE'}> +
<[Date Created]={'<$(=MonthStart(Today()))'}, [Date Closed] = {< Last12Months = {1}>}> }DISTINCT [Issue ID])