Expression Problem

[#1]

Dear Fellow Users,

I am struggling with an expression issue those days, maybe some of you could have a solution for me.

In my job, people book a service that they use later on.

I would like to compare what I have on the books today to what I had on the books last year at the same time.

The field “BookDate” tells me the date of the reservation

I have in a chart those expressions:

The Revenue I have on the books this year:  SUM(ProdRev)

The revenue I generated last year:  SUM({$<Year_Name={$(=MAX(Year_Name)-1)}>}ProdRev)

Basically, I would like to have the revenue I generated last year for all values of "BookDate" that are below (today-364). Any Idea of how I could do that ?

[hic]

Take a look at

Year-over-Year Comparisons

and

Calculating rolling n-period totals, averages or other aggregations

You can also search the community for threads with "Year-To-Date"

HIC

[#3]

Hi Henric,

Thanks for your answer. However, this is not exactly what I meant.

Let’s say you are managing a transportation company.

3 things will be interesting for you:

1. Revenue you have on the books for next days/months, for me would be sum(ProdRev), Bleu line in the Graph.

2. How you performed last Year for me : SUM({$<Year_Name={$(=MAX(Year_Name)-1)}>}ProdRev) This is what you call YoY. Orange Line in the Graph.

3. What you had on the books last year at the same time (bookings made before March 05 2014 for last year), Grey line in the Graph

Basically, what I would like is an expression that says : ”SUM({$<Year_Name={$(=MAX(Year_Name)-1)}>}ProdRev) for BookDate<today-365”.

Have a nice day !

[yura_ratu]

As I undertand you have two dates - booking date and date of using transport service, wright? Could you give an example of data you have

[hic]

You say that you want a condition like BookDate<today-365. This is exactly what I define in the blog post Year-over-Year Comparisons in the formula

  If( DayNumberOfYear(Date) <= DayNumberOfYear(Today()), 1, 0 ) as IsInYTD,

Here you get a Boolean field that says whether the date is before the same time of the year as today.

So, all I think you need to do, is to define such a field in the script, based on your booking date, and then use this field in a Set Analysis expression, for example

  Sum( {$<Year_Name={$(=MAX(Year_Name)-1)}, IsInYTD={1}>} ProdRev )

HIC