8 Replies Latest reply: Oct 21, 2011 5:31 AM by Jerome Hanff

# YTD

Hello

i want to calculate YTD and YTD -1 .

i have this right now

sum({\$<YEAR = {\$(=max(YEAR))}, MONTH = {"<=\$(=max({<YEAR={\$(=max(YEAR))}>} MONTH))"}>} VALUESALES)

sum({\$<YEAR = {\$(=max(YEAR - 1))}, MONTH = {"<=\$(=max({<YEAR={\$(=max(YEAR))}>} MONTH))"}>} VALUESALES)

But what i want is to go do the exact day in the month, not just the month

ex: from (YTD-1) 01.01.2009 to 19.10.2009 and 01.01.2010 to 19.10.2010

any idea how to complete this function to do so?

• ###### YTD

Hi Jerome,

There is a very similar issue solved few hours ago in this thread.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

• ###### Re: YTD

I have this for 2010 (Year -1 ) but how can i have the same for 2009 2008 and 2007 ?

• ###### Re: YTD

I would rather prefer to do it directly in the definition field of the chart

can you help me?

• ###### Re: YTD

i want an expression that is valable for all the years in the chart, not only for year -1 (see image)

in BLUE ist the year sales from 01.01.YYYY to 31.12.YYYY

i want a second bar in the chart for each year with the sales from 01.01.YYYY to TODAY (pex 20.10.YYYY)

• ###### Re: YTD

Hi Jerome,

Then use one dimension (Year) and two expressions, one without filter that will return the aggregated value of SALES per year

```SUM(SALES)
```

And the other expression using the Date field compared to the result of the YearToDate function (that will return true/-1 if the date is between 01/01 and today)

```SUM({< BDLE_D_DATE = {"=YearToDate(BDLE_D_DATE, Year(BDLE_D_DATE) - Year(Today())) = -1"} >} SALES)
```

However, this expression may not work because the YearToDate() function as everyting in the set analysis is evaluated once and for the whole chart, before rendering it, and not row by row. In this second case, you will need to use a If() condition that will slower your chart, and make cumbersome to write and debug your expressions, or even to create a caculated dimension to get only those dates that match the YTD.

Is far easier to maintain and to code the field in the Calendar table and use that flag later on in your tables, hence my suggestion in the attached applications.

Hope that makes sense.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

• ###### Re: YTD

GREAT!!! it's working!!!

thany very very much!