5 Replies Latest reply: Dec 14, 2013 6:31 AM by Jeroen Jordaan

# Sales per date for 10 days earlier

Hello everyone,

I hope somebody can help me with this.

A customer needs to have a Straight or pivot table with the dimension date.

For me this is the easy part :-)

As an expression I need a formula that is calculating the sales but only for 10 days earlier.

See the example below

DateSalesSales 10 days Earlier
2013-11-21100 for the date 2013-11-21This should be the sales from the date 2013-11-11
2013-11-20250 for the date 2013-11-20This should be the sales from the date 2013-11-10
2013-11-19175 for the date 2013-11-19This should be the sales from the date 2013-11-09
2013-11-18150 for the date 2013-11-18This should be the sales from the date 2013-11-08

This table also needs to change to a line graph.

• ###### Re: Sales per date for 10 days earlier

While technically you can create an expression to calculate this (see Evaluating "sets" in the context of a dimension) I think you're better off adding a table in your data model that links every date with its 10-days earlier partner-date. A kind of AsOf table (see for example Calculating rolling n-period totals, averages or other aggregations). The AsOf table you'd need would be something like:

ReportDate, Date, Type

2013-11-21,2013-11-21,Current

2013-11-21,2013-11-11,10DaysEarlier

2013-11-20,2013-11-20,Current

2013-11-20,2013-11-10,10DaysEarlier

...etc

You can then use ReportDate as dimension and expressions like sum({<Type={'Current'}>}Sales) and sum({<Type={'10DaysEarlier}>}Sales).

• ###### Re: Sales per date for 10 days earlier

Gysbert,

Thank you (as always) for your help.

• ###### Re: Sales per date for 10 days earlier

Something like this should work:

=sum({< Date = {'\$(=date(today(0)-10))'}>} Sales)

Best,

Matt

• ###### Re: Sales per date for 10 days earlier

Hi Jeroen,

Did either of these solutions work for you?

Matt