How to carry forward Formula Indicator value from previous month to next month

urbishnusanyal · ‎07-12-2015

I have a requirement where I have to compute the difference between opened and closed incident in a particular month and after that I have to carry forward the balance to next month to sum up with next month's opened-closed difference. This way I have to show the graph for a year,

I have two automated indicators which fetch the Opened and closed incident count and one formula indicator which computes the difference. It is working fine for the first month of the year but I am not able to pass the previous month's value to next month.

Can anyone please let me know how will I be able to solve this out.

Vincent Loffel1 · ‎07-13-2015

Hi Urbishnu,

Strange that it doesn't provide you with the desired result. It should sum all values prior to the collection date. But anyway, here is how you can create a manual indicator that gets its values by a script. As i do not know you instance i can only give you the directions in how to fill the indicator. The calculations on how to get the value you need is something i can not describe.

But always be careful when writing to the pa_scores table with a script. Test it thoroughly because the pa_scores table is the table that holds all scores of your performance analytics collection. Be absolutely sure no scores of of other indicators are changed.

First you need to create a manual indicator.

Then you need to create a script that writes the score of your calculation to the table pa_scores. You can view the table by using pa_scores.list

The script might look something like this:

var indicator = '[sys_id of manual indicator]';

var value = '[value of your calculation]';

var start_at = '[date of the score collection]'

if (value && value != '') {

var gr = new GlideRecord('pa_scores');

gr.initialize();

gr.setValue('indicator', indicator);

gr.setValue('start_at', start_at);

gr.setValue('value', value);

//if you create breakdown's you have to create a loop to add a score per indicator/breakdown

gr.insert();

gr.close();

}

The script then has to be scheduled before the performance analytics collection job runs to use it in for instance another formula indicator.

Regards,

Vincent

View solution in original post

Vincent Loffel1 · ‎07-13-2015

Hi Urbishnu,

I think what you need is to use the default timeseries for your formula indicator. By selecting "Year to date AVG" or "Year to date SUM" under 'Other', you create a report that sums or averages the values you calculated.

Another option is to create a manual indicator and a script that fills the manual indicator each month. This goves you more freedom in your calculation, but may be a bit harder to create.

Regards,

Vincent

urbishnusanyal · ‎07-13-2015

Hi Vincent,

I have tried that Year to Date Sum but it is not providing me desired result.

However I didn't get you how to write a script to populate value in manual indicator. Can you please elaborate this.

Vincent Loffel1 · ‎07-13-2015

Hi Urbishnu,

Strange that it doesn't provide you with the desired result. It should sum all values prior to the collection date. But anyway, here is how you can create a manual indicator that gets its values by a script. As i do not know you instance i can only give you the directions in how to fill the indicator. The calculations on how to get the value you need is something i can not describe.

But always be careful when writing to the pa_scores table with a script. Test it thoroughly because the pa_scores table is the table that holds all scores of your performance analytics collection. Be absolutely sure no scores of of other indicators are changed.

First you need to create a manual indicator.

Then you need to create a script that writes the score of your calculation to the table pa_scores. You can view the table by using pa_scores.list

The script might look something like this:

var indicator = '[sys_id of manual indicator]';

var value = '[value of your calculation]';

var start_at = '[date of the score collection]'

if (value && value != '') {

var gr = new GlideRecord('pa_scores');

gr.initialize();

gr.setValue('indicator', indicator);

gr.setValue('start_at', start_at);

gr.setValue('value', value);

//if you create breakdown's you have to create a loop to add a score per indicator/breakdown

gr.insert();

gr.close();

}

The script then has to be scheduled before the performance analytics collection job runs to use it in for instance another formula indicator.

Regards,

Vincent