Get the variance between the last week sum and the avg of six-week sum

[mikegrattan]

Here's the formula for prior week's Sum:  =sum( {$<DateFlag_LastWeek={1}>}EquivalentQuantity)

Here's the formula for six week Sum:  =sum({<DateFlag_SixWeek={1}>}EquivalentQuantity)

The six week column is set up to show the "total" as the average:

The variance column subtracts the six week sum from the prior week sum:

=sum( {$<DateFlag_LastWeek={1}>}EquivalentQuantity)-sum( {$<DateFlag_SixWeek={1}>}EquivalentQuantity)

I would like the last value of the Variance column, which currently shows a zero, to show the difference between the prior week sum and the 6wk column's "total", which is 114,941 in the screen shot.  So, instead of showing a zero, it would show -2186.  I came up with a formula that would give me the result, but it is working on all weeks and I think I need some kind of hybrid formula that gives me the current results in the variance column, but then additionally replaces the zero with the results from this formula:  

=sum( {$<DateFlag_LastWeek={1}>}EquivalentQuantity) -
avg(total aggr(sum ({<DateFlag_SixWeek={1}>} EquivalentQuantity),Week,ParentDescription))

Is there a way to use the main variance formula for weeks 2-6 and the second formula for week 1?

[Kushal_Chawda]

@mikegrattan  do you have sample to work on?

[mikegrattan · Accepted solution]

Hi Kushal,

I don't have a sample to work on but I could make one if necessary.  I was able to get good results with the following but I'm not sure it's the best approach:

=If(
sum( {$<DateFlag_LastWeek={1}>}EquivalentQuantity)>0,sum( {$<DateFlag_LastWeek={1}>}EquivalentQuantity) -
avg(total aggr(sum ({<DateFlag_SixWeek={1}>} EquivalentQuantity),Week,ParentDescription)),sum( {$<DateFlag_LastWeek={1}>}EquivalentQuantity)-sum( {$<DateFlag_SixWeek={1}>}EquivalentQuantity))