2 Replies Latest reply: Oct 10, 2016 11:13 PM by Steven Bourgeois

# Calculate stock of event on going over the months

Hello Everyone,

In order to manage a stock of on-going tasks, I would like to compare their creation and resolution date. And to follow up the trend in a line graph.

Here are my data :

 Key StatusTask Month A1 Created Sep-15 A2 Created Oct-15 A3 Created Nov-15 A4 Created Dec-15 A5 Created Jan-16 A6 Created Feb-16 A7 Created Mar-16 A8 Created Apr-16 A9 Created May-16 A10 Created Jun-16 A1 Resolved Jan-16 A2 Resolved Feb-16 A7 Resolved Apr-16 A8 Resolved May-16 A5 Resolved Jun-16 A3 Resolved Jul-16

Results I would like to get and then to show in a line chart

 Month Still opened Sep-15 1 Oct-15 2 Nov-15 3 Dec-15 4 Jan-16 4 Feb-16 4 Mar-16 5 Apr-16 5 May-16 5 Jun-16 5 Jul-16 4

For a given month, the following code is working perfectly, but I am not able to make it work in a line graph with Date as abscissa for whatever reasons:

count( {< StatusTask = {Created}, Month = {'<=\$(=max(Month))'} >}Key)  - count( {< StatusTask = {Resolved}, Month = {'<=\$(=max(Month))'} >}Key)

As well, in the graph I have tried unsuccessfully:

aggr(count( {< StatusTask = {Created}, Month = {'<=\$(=max(Month))'} >}Key)  - count( {< StatusTask = {Resolved}, Month = {'<=\$(=max(Month))'} >}Key), Month)

Once in the line Graph, the result is always given me the difference between Created and Resolved on only one month instead of all the past months ( Month = {'<=\$(=max(Month))'} )

Do you have any idea, how could I solve this issue?

Steven

• ###### Re: Calculate stock of event on going over the months

I think it is easy to do with manipulation in the script.

Script:

Table:

MonthName(Date#(Month, 'MMM-YY')) as Created_Month

FROM

(html, codepage is 1252, embedded labels, table is @1)

Left Join (Table)

MonthName(Date#(Month, 'MMM-YY')) as Resolved_Month

FROM

(html, codepage is 1252, embedded labels, table is @1)

FinalTable:

Created_Month,

Resolved_Month,

MonthName(AddMonths(Created_Month, IterNo() - 1)) as Date

Resident Table

While AddMonths(Created_Month, IterNo() - 1) <= If(Len(Trim(Resolved_Month)) = 0, MonthName(Today()), Resolved_Month);

DROP Table Table;

• ###### Re: Calculate stock of event on going over the months

Perfect it is exactly what I was looking for, I didn't think to work directly on the Script , Thank you very much.

I have just modified a line since I want the resolution month to be out of the "stock" (I didn't mention it in my original post)

if(MonthName(AddMonths(Created_Month, IterNo() - 1)) = Resolved_Month, Date#('Jan-1999', 'mmm-yyyy'),MonthName(AddMonths(Created_Month, IterNo() - 1))) as Date

Then exclude Jan-1999 of my analysis