Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Date | Sales | Sales 10 days Earlier |
---|---|---|
2013-11-21 | 100 for the date 2013-11-21 | This should be the sales from the date 2013-11-11 |
2013-11-20 | 250 for the date 2013-11-20 | This should be the sales from the date 2013-11-10 |
2013-11-19 | 175 for the date 2013-11-19 | This should be the sales from the date 2013-11-09 |
2013-11-18 | 150 for the date 2013-11-18 | This should be the sales from the date 2013-11-08 |
This table also needs to change to a line graph.
Thanks in advance for your time
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).
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).
Something like this should work:
=sum({< Date = {'$(=date(today(0)-10))'}>} Sales)
Best,
Matt
Hi Jeroen,
Did either of these solutions work for you?
Matt
Sorry for the late reply.
Thank you for your help.
Gysbert,
Thank you (as always) for your help.