Error is calculation on Formula Indicator with Week as Average

[sagarsolanki]

Hello,

I am facing an issue with one of my formula indicator that I have implemented.

The indicator is called "Average Age of Closed Tech Assessments Tasks" with formula ([[Summed Duration of Closed TATSK Tickets]] / [[Number of Closed TATSK Tickets]] / 24) || 0

I am sharing the indicator scores for these two indicators.  Assessment Type is the Breakdown and these indicators collect data in a Daily Collection job.

My math says that lets say for Assessment Type = Accessibility, the score should be 6.78.
Summed Duration of Closed TATSK Tickets:
100 + 2031 + 1153 + 622 = 3906
Number of Closed TATSK Tickets:
3 + 8 + 7 + 6 = 24

Score for formula indicator = 3906 / 24 / 24 = 6.78125

However when I view this indicator on my dashboard using "BY WEEK AVG", it shows 3 days as below

My math says that this is happening because PA is averaging everyday:

Date	Summed Duration (Hours)	Number of Tickets	Daily Average (Hours)[Duration / Count]	Daily Average (Days)[Hours / 24]
May 18	100	3	33.33 (100/3)	1.39 (33.33/24)
May 19	2,031	8	253.88	10.58
May 20	1,153	7	164.71	6.86
May 21	0	0	0	0
May 22	622	6	103.67	4.32
May 23	0	0	0	0
May 24	0	0	0	0

and then its is performing this calculation

Resulting into ~3 days.

How can I fix this please?

[PoonkodiS · Accepted solution]

Hi @sagarsolanki 

To ressolve this issue try this:

To resolve this, the Apply timeseries to result field on the Formula Indicator record needs to be set to False (unchecked).

According to ServiceNow KB logic, setting this to False forces PA to aggregate the contributing base indicators first (calculating the weekly average per column) before executing the formula division. This eliminates the impact of the zero-activity days and yields the correct weighted average of 6.78 days

When Apply time series to result is not checked: Each contributing indicator is evaluated first, and then the formula is evaluated.

• It grabs the total hours for the whole week:  3,906 hours.

• It grabs the total tickets for the whole week: 24 tickets.

• It does math problem: It runs the formula calculation at the very end using those weekly totals:

3,906/24 *24 ​=6.78 days

Refer :

https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0639231

Docs | ServiceNow

https://www.servicenow.com/docs/r/yokohama/now-intelligence/performance-analytics/apply-time-series-...

Hope it helps

[sagarsolanki]

Thank you! It worked as expected. Can't believe I missed this! One small click and there I was spending hours trying to figure it out.

Appreciate your response.

[PoonkodiS]

Thank you for accepting my solution and for the thumbs up. It encourages me to stay more interested and keep contributing. Glad ! You sorted it.

[PoonkodiS]

Hi @sagarsolanki 

To ressolve this issue try this:

To resolve this, the Apply timeseries to result field on the Formula Indicator record needs to be set to False (unchecked).

According to ServiceNow KB logic, setting this to False forces PA to aggregate the contributing base indicators first (calculating the weekly average per column) before executing the formula division. This eliminates the impact of the zero-activity days and yields the correct weighted average of 6.78 days

When Apply time series to result is not checked: Each contributing indicator is evaluated first, and then the formula is evaluated.

• It grabs the total hours for the whole week:  3,906 hours.

• It grabs the total tickets for the whole week: 24 tickets.

• It does math problem: It runs the formula calculation at the very end using those weekly totals:

3,906/24 *24 ​=6.78 days

Refer :

https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0639231

Docs | ServiceNow

https://www.servicenow.com/docs/r/yokohama/now-intelligence/performance-analytics/apply-time-series-...

Hope it helps