Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Thanx in advance for your help
Hi Jerome,
There is a very similar issue solved few hours ago in this thread.
Hope that helps.
BI Consultant
=Sum({$<ANNEE={$(=Max(ANNEE)-1)}, BDLE_D_DATE={'<=$(=AddYears(vDate,-1))'}>} SALES)
I have this for 2010 (Year -1 ) but how can i have the same for 2009 2008 and 2007 ?
Hi,
I'd rather use the flags in the calendar table so you can show as many years comparing to the current as you want. You can even create a flag that will return true for all dates of al years from January 1 to today for 2009, 2010, 2011...
Check the attached example.
Hope that helps.
BI Consultant
I would rather prefer to do it directly in the definition field of the chart
can you help me?
Hi Jerome,
I'm afraid I don't understand quite well what you are up to. Would you please elaborate or upload some sample data so we can see what do you want to do and how to get the best solutions?
Regards.
BI Consultant
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)
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.
BI Consultant
GREAT!!! it's working!!!
thany very very much!