
Re: Calculate Sum of X on a date and Y on a date N days earlier
Sunny Talwar Apr 7, 2017 4:23 AM (in response to Matthew Lucas)I would either use the The AsOf Table or something like this
Sum(x)
Above(Sum(y), 3)
or this
Above(Sum({<Date>} y), 3) * Avg(1)

Re: Calculate Sum of X on a date and Y on a date N days earlier
Matthew Lucas Apr 7, 2017 4:16 PM (in response to Sunny Talwar )Thank you, this puts me way closer to the solution I'm looking for. This seems to work while Date is the dimension. Is there a way to make this work so that a weekly or monthly view also produces the same values?
For example:
The bar for Sum(x) over Febuary 2017 should be Sum(x) from 2/1  2/28
The bar for Sum(y) over February 2017 should be Sum(y) from 1/29  2/25.
What I've tried is: Below(Sum(Aggr(Sum(y), Date)), 3)
But when I switch from Date to Month, the bar shows from 3 months ago instead of 3 days ago.
I feel like I'm confusing the order things take place in or what the functions Aggr/Below are actually doing. I expect Aggr to produce a virtual table at the date level, and Below to operate on that, but it's still operating on the displayed dimension or the actual table.

Re: Calculate Sum of X on a date and Y on a date N days earlier
Sunny Talwar Apr 8, 2017 6:57 AM (in response to Matthew Lucas)May be try something like this
Sum(Aggr(Below(Sum(y), 3), (Date, (Numeric, desc))))
or
Sum(Aggr(Above(Sum(y), 3), (Date, (Numeric))))

