Calculate Moving average

[Oluwaseun]

Hi, I am still quite new to the use of the Google App-sheet and formulas, Pardon me.

So Let me be as basic as possible. I am trying to Find out the Moving average for a scorecard with Four (4) columns

• Customer Engagement
• Customer interactions
• Process Compliance
• Issue Resolution

I want App-sheet to give me an overall average even if I do not capture a value for any of the columns (which I regard as Not Applicable)

I currently use the formula 

(AVERAGE(LIST([Customer Engagement],[Issue Resolution],[Process Compliance],[Customer Handling]))), but it does not ignore a column if I don't capture a value for it

Thanks

[dbaum]

---

@Oluwaseun wrote:

ignore a column if I don't capture a value for it

---

You could subtract from the list any blank (or zero or other) values that you want to exclude.

---

@Oluwaseun wrote:

Moving average

---

If you mean the last n values, you may need to use the TOP function.

[Aurelien]

---

@dbaum wrote:

You could subtract from the list any blank (or zero or other) values that you want to exclude.

---

@Oluwaseun this is what you could use:

AVERAGE(
  LIST([Customer Engagement],[Issue Resolution],[Process Compliance],[Customer Handling])
  -LIST("")
)

@lizlynch I think it could be relevant to add the example of removing blank items on the documentation "Substract values from the list", as this question comes quite regularly (just a suggestion 🙂 )

[Oluwaseun]

THanks @Aurelien @dbaum for the feedback, tried it, but not working as expected see attached

My Overall Score right now should be 66.66% since only 3 Columns have values

This as well

Score should be 83.3% but still 50%

[gregdiana1]

It appears you want to do (5+10+5)/30 = 66% and (5+10+10)/30=83.3% but that is not what the formula you guys are haggling about is doing.

The Customer Engagement value of 5 is what? The count or the average or the moving average but it is certainly not a LIST?

So ([Customer Engangement]+[Issue Resolution] + [Process Compliance ])/30 provides the answer you are seeking.

So SUM(LIST([CE],[IR],{PC]+[CH] - LIST(""))) = SUM(LIST(5,10,10,0) - LIST("")) = SUM(LIST(5,10,0)) =  15 is incorrect as it simply removes duplicates and not the zeroes ?

That is why you get 50% for each scenario as each results in SUM(10+5)/30 = 15/30 = 50%.

So as I said you are only getting what you are programming APPSHEET to provide

The numerator is not the problem it is the denominator that is the problem and what you want is

(CE + IR + PC + CH)/(MAX(CE)+MAX(IR)+MAX(PC)) where the numerator excludes any column which is zero

So  you simply have to test how many are 0 and reduce the NUMERATOR like

10*NONZERO so you would get in each case above 10*3 or (5+10+5)/10*3 = 20/30 = 66.66% etc

[TeeSee1 · Accepted solution]

I was curious and did a quick test.

Avg Score: This, I believe, gives the expected results by @Oluwaseun 

SUM(LIST([fld1],[fld2],[fld3],[fld4]))
/ 
(
 if(isnotblank([fld1]),1,0) +
 if(isnotblank([fld2]),1,0) + 
 if(isnotblank([fld3]),1,0) + 
 if(isnotblank([fld4]),1,0)
)

This takes a score of 0 (zero) as a valid score. You can add a condition to deal with that if you want.

Avg Score 2

AVERAGE(
 LIST([fld1],[fld2],[fld3],[fld4]) - LIST("")
)

List Sub Result

LIST([fld1],[fld2],[fld3],[fld4]) - LIST("")

Apparently list subtraction reduces its resultant list to unique elements and does not fit here.

[Oluwaseun]

The Avg Score works perfectly. I am so Happy Thanks @TeeSee1  Thanks @Aurelien Thanks @dbaum  Thanks @gregdiana1 for all contributions

[lizlynch]

@Aurelien Thank you for the ping! I've created a ticket to implement this change in the near future. 

[gregdiana1]

As I see matters you are conflating two separate issues.

Firstly a running or moving average filter will take several samples either before, after or on either side of the current data sample and the function you are using above cannot do that.

Try first using excel or Tableau Public 

Secondly, then you wish to additionally also average several running or moving average columns which will not work if the first step is not correct.

APPSHEET can't give anyone anything unless they program and use APSHEET correctly, the old "junk in junk out" adage.

I think you need to apply your mind a little to this one.

[Aurelien]

Thanks for the feedback. Can you try to break the expression on another column, to analyze where this issue may comes from ?

I would suggest trying first: 

LIST([Customer Engagement],[Issue Resolution],[Process Compliance],[Customer Handling])

 Then:

LIST([Customer Engagement],[Issue Resolution],[Process Compliance],[Customer Handling])
  -LIST("")

Can you try and check the output of these calculations?

[Oluwaseun]

Hi @Aurelien  Thanks for this see results below 

LIST([Customer Engagement],[Issue Resolution],[Process Compliance],[Customer Handling])

LIST([Customer Engagement],[Issue Resolution],[Process Compliance],[Customer Handling])
  -LIST("")

On both scenarios, the expected result should be 83.3%

[gregdiana1]

Yes and because what you are all trying to do is incorrect!

This simply removes the duplicates so you get (10+5)/30 = 50%

What you want is (10+10+5)/30 = 83.3%

So you want SUM(LIST)/SUM(NON ZERO)*10 =(10+10+5+0)/3(NON ZERO)*10 =25/30=83.3%

Of the 4 columns 3 are non-zero hence denominator = 3*10 = 30

LIST([Customer Engagement],[Issue Resolution],[Process Compliance],[Customer Handling])
  -LIST("")

 Produces (10+5+0) as it removes duplicates from (10,10,5,0) hence you get 10+5+0 = 15/30 = 50%

see

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Count-how-many-times-an-item-appear-in-a-text-l...

[dbaum]

So, the blank value is being treated as 0.

• Without list subtraction: The values are 10, 5, 10, and 0, which average to 62.5%
• With list subtraction: The values are 10, 5, and 0 (the duplicate 10 value is removed since list subtraction is in part equivalent to applying the UNIQUE function), which average to 50%

I tested the following brute force approach of applying a condition to the inclusion of each value, and still encountered the issue--the result was 62.5%:

AVERAGE(LIST(IFS(true, 10), IFS(true, 5), IFS(true, 10), IFS(false, 0)))

You may instead need to arithmetically calculate the average. This test indeed yielded 83.3%:

SUM(LIST(10, 5, 10, 0)) / SUM(LIST(IF(ISBLANK(10), 0.0, 1.0), IF(ISBLANK(5), 0.0, 1.0), IF(ISBLANK(10), 0.0, 1.0), IF(ISBLANK(""), 0.0, 1.0)))