Calculate Sum of X on a date and Y on a date N days earlier

[#1]

I'm trying to create a chart that shows a bar for Sum(x) on a date, a bar for Sum(y) on (date-3), and date is the dimension.

So for example, how could I produce a chart that has the following dimensions/measures:

1/5/2017	1/6/2017	1/7/2017	1/8/2017	1/9/2017
Sum(x) on 1/5	Sum(x) on 1/6	Sum(x) on 1/7	Sum(x) on 1/8	Sum(x) on 1/9
Sum(y) on 1/2	Sum(y) on 1/3	Sum(y) on 1/4	Sum(y) on 1/5	Sum(y) on 1/6

The source data is one table with a bunch of different dimensions that the user should be able to filter by:

Date	Dimension 1	Dimension 2	Dimension N	x	y
1/1/2017	A	C	...	1	2
1/2/2017	B	D	...	3	4
...	...		...	...	...

My first thought was to use Set Analysis, but after hours of searching, I'm not sure if this is possible.

Does anyone know of any way to accomplish this using any method?

Basically I'd want something like this:

Sum(x)

Sum({<Date={Date-3}>}y)

So that the bar of Sum(y) from the date 1/2/2017 is shown above the date 1/5/2017.

Please let me know if you need any additional details.

Thanks!

[sunny_talwar · Accepted solution]

I would either use the The As-Of Table‌ or something like this

Sum(x)

Above(Sum(y), 3)

or this

Above(Sum({<Date>} y), 3) * Avg(1)

[#3]

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.

[sunny_talwar]

May be try something like this

Sum(Aggr(Below(Sum(y), -3), (Date, (Numeric, desc))))

or

Sum(Aggr(Above(Sum(y), -3), (Date, (Numeric))))