Cumulative average of weekly numbers by day of week for past six weeks?

[mikegrattan]

Here's the scenario:

• Get the average units for the past six weeks by day of week
• Starting with Monday's six week average, accumulate the total for each day of the week
• Chart the data showing Monday through Friday, with their respective cumulative totals

The snippet below shows an example and I'm attaching a sample app.  At this point, I've experimented with some Rangesum expressions and tried aggregating the data in the data load but wasn't happy with the results.  The sample app does not aggregate the data, but please feel free to try that approach if you think it will be better than using complicated Rangesum formulas.  Thank you for taking a look!

[Chanty4u]

Hi 

Do below things 

WeekDay(Date) as DayOfWeek

Avg({<Date={"$(">=$(=WeekStart(Max(Date)-6*7)))<=$(=Max(Date))"}>} Units) as AvgUnits

Above(Sum(AvgUnits),0,RowNo()) as CumulativeAvgUnits

[Kushal_Chawda]

@mikegrattan  if your DateFlag_SixWeek is correct you can use below expression with Dimension DayOfWeek

avg(total <DayOfWeek>aggr(sum({<DateFlag_SixWeek={1},CommodityCode={'LTC'}>}CurrentSold), DayOfWeek, Date))

[mikegrattan]

I just got back in the office and will take a look at your suggestions.  Thank you very much for your responses.

[mikegrattan]

Kushal,

When I use your expression, the numbers seem to repeat every week for the last six weeks.  I'm trying to get an end result that shows the average for the last six Mondays in the Monday row; the accumulation of the Monday number added to the average of the last six Tuesdays for the Tuesday number; the accumulation of the Tuesday total added to the average of the last six Wednesdays for the Wednesday number, etc.

Update:  If I remove all other dimensions except DayOfWeek, the numbers from your expression seem to be identical to just using a straight average...they are not cumulative.  However, I used the built-in "Modifier" functionality and the output expression, while pretty crazy looking, appears to be giving me the correct results (I did a slight modification of your original expression to exclude Sunday):

RangeSum(Above(If(Count([$(=Replace(GetObjectField(0),']',']]'))]) > 0, (　Avg({<CommodityCode={'LTC'},DateFlag_SixWeek={1},DayOfWeek-={'Sun'}>}CurrentSold)　) + Sum({1<[$(=Replace(GetObjectField(0),']',']]'))]={">=$(=Min([$(=Replace(GetObjectField(0),']',']]'))]))<=$(=Max([$(=Replace(GetObjectField(0),']',']]'))]))"}>}0), 0), 0, 6))

[mikegrattan]

Hi Chanty4u,

I'm not quite sure where this all goes.  Are these expressions to be used in a table as new measures or does this go in the data load script? 

I did look at putting it in the data load script, but the set expressions don't seem to work there. 

I also looked at using the expressions in the table, but the Above expression doesn't seem to work on the AvgUnits measure (I added the Avg formula and called it AvgUnits, then added a new measure called CumulativeAvgUnits as you specified, but the Above formula doesn't evaluate against the AvgUnits measure as it doesn't recognize it as a valid name of a measure).