Calculating a Rolling Average Containing Empty Values

John_Fonte
4 mths ago
10replies
Answered

I noticed that whenever a cell is empty, Pyramid excludes it from the average. For example, if week1 = 1, week2 = 0, week3 = 5, Pyramid excludes the week 2 element from the calculation (i.e., (1+5)/2 as opposed to the correct (1+0+5)/3). This makes average calculations - especially rolling average calculations, useless. I would like to include all weeks in the calculation, with weeks of no activity having a value of 0.

Because Pyramid's behavior excludes the week, as opposed to including it and calling it NULL, IfNull logic does not work. Moreover, IsEmpty is not supported. Finally, having a zero "fill blank" functionality was requested but Not Planned.

Any help would be greatly appreciated!

- Lead Consultant Advanced Analytics
- Michael_Daun
- 4 mths ago
- Reported - view
Hi John,
we already had this problem, too.

In the end, it is rather a data model problem: I assume that the data point "week2 = 0" is not part of the data, this particular fact does not exist. Therefore, Pyramid counts only two rows and calculates the average accordingly. Even if you would manage to visualize week2 in the grid (probably via outer joins in the model and via "Show Empties" in the visualization) this week is not available for the calculation.

So - as a solution/workaround: Is it possible to fill up your data with explicit zeros for "empty" weeks?

Best regards,
Michael
- John_Fonte
  
  4 mths ago
  
  Reported - view
  Hey Michael. I appreciate the response. What could the custom column be that would achieve creating explicit zeroes for empty weeks? I can't even form the psuedocode in my mind to create that, let alone actually script that out.
- Lead Consultant Advanced Analytics
  
  Michael_Daun
  
  4 mths ago
  
  Reported - view
  , no, I do not think of Custom Columns but deeper in the backend: Is it possible to fill up your data in the database? So, can the loading process (ETL) be changed to have explicit zero facts in the data? Or, in Pyramid, can the data model be changed to fill up the empty weeks with zeros?
- Lead Software Engineer
- Vijayan_Krishnan
- 3 mths ago
- Reported - view
Hi John,

I tried using the week number. Please let me know if that helps.

Example : 2018 week 4 ---> 1641009.35/4=410252.34. we don't have data for some weeks like week 1, 2, 3, 5, 6, ...

Thanks & Regards

Vijayan Krishnan
- John_Fonte
  
  3 mths ago
  
  Reported - view
  Hi can you explain what is happening here? Does this work when there are multiple empty values or non-consecutive empty values?
- Lead Software Engineer
  
  Vijayan_Krishnan
  
  3 mths ago
  
  Reported - view
  This approach handles multiple empty values within the column. In this example, data is missing for most weeks except for weeks 4, 9, 13, 18, and so on.
  
  In the 'Date Week' column, not all weeks are represented. For now, I'm using the amount relevant to each week and dividing it by the week number (using the Caption). To find the cumulative average, I utilized CumulativeTimeSum and divided it by the week number. EX: week 4 = 1641009.35/4 | cumulative AVG 1641009.35/4, week 52 = 745521.6/52 | cumulative AVG 13896468.27/52
  
  Let me know if this explanation helps. Feel free to reply with any questions you may have.
- John_Fonte
  
  3 mths ago
  
  Reported - view
  That explanation was a huge help, thank you. It seems like the main limitation to this method is that it can only be used for a single year - is that correct? I feel like the if-then logic to accommodate weeks in other years would get very complicated very quickly.
  
  I am going to use this method - thanks so much for your help!
- Lead Software Engineer
  
  Vijayan_Krishnan
  
  3 mths ago
  
  Reported - view
  it will work for multi year too
  
  Thanks
  
  Vijayan
- Senior Director of Product
- Ian_Macdonald
- 3 mths ago
- Reported - view
Hi

In some circumstances you can also use the built in "Fill the Blanks" function in Discover.

Here we have Returns by full week with Show Empties on:

Clicking on the Fill the Blanks button in the Ribbon Menu, you can add (or swap in) a new column with the blanks filled in.

An explanation of the action is also inserted into the Notes section of your Discover view.

Hope that helps.

Ian
- John_Fonte
  
  3 mths ago
  
  Reported - view
  I appreciate the help Ian. Unfortunately, this is really only accurate if there are only a handful of values that need to be imputed. I have stretches of multiple weeks in a row without values.