Calculating Average Over Time
I have a formula that calculates an average. I have the average for each month in an answer ( so one value for each month in the year). What I would like to do is calculate the average of this average at a given point in time. For example my data is:
'Month' 'Month Average' 'YTD Average'
January 80% 80%
February 60% 70%
March 90% 76.67%
April 85% 78.75%
The 'YTD Average' would be a sum of all the 'Month Average' divided by the number of months in the year at that given point. I've tried average('Month Average') in a second formula but error message shows:
'ThoughtSpot did not find Month Average in your data or metadata'
Would this be possible with 'moving_average' or another formula?
I wrote this attached document where section 3.b Semi-Additive Multiple Dates provides a solution.
Alternatively a really clean solution that keeps the formulas much more simple is to just create a numerator and denominator ratio.
I.e. Assume we have the model where we have a Fact with say End of Day Balance and balance date and a Date Dimension with Date and a second column, Date Count, with a value of 1 for historical dates and 0 for future dates.
The Average Daily Balance is Sum (End of Day Balance) / Sum (Date Count).
If you use the Date column from the Date Dimension you can then change the grain and select any period and have the correct daily average.
I.e. This Year Monthly will return the Daily Average for this years data for each month.
You could change the grain to Quarterly, Weekly etc.
Thanks Damian Waldron Though useful, I don't think this quite gets me the result I am after in this case. I am not looking for the daily average at month, quarter, or year grain. I need to calculate the average as the year progresses. For example, given the values above, the average YTD value for January would be 80 (80/1), February would be 70 ((80+60)/2) and March would be 76.67 (80+60+90)/3). Moving_average seems like a good solution for this case. When I try to use my formula, Month Average, with moving_average I am getting a numeric error.
The formula for 'Month Average' is:
if(sum(mainhoursfield)>0 then sum(mainhoursfield)/(sum(hoursfield1)-sum(hoursfield2)) else 0
Can moving_average accept a formula like this as the measure?
I am trying:
moving_average(Month Average, 11,0,datefield)