4 Replies Latest reply: Feb 14, 2014 1:59 AM by Soumya Gowda

YTD based on selections

Dear all,

I want to be able to compare last years figures and this years figures based on current selections.  Meaning that if I select 2013 and February, I will compare January + February this year against January and February last Year. I have made the following expression for this year, but have not been able to make an expression for last year.

=Sum({< År={\$(=Max(År))},Kvartal=,Måned=,DatoNum={'>=\$(=YearStart(Max(DatoNum)))<=\$(=Max(DatoNum))'}>} FaktBeløp)

År=Year

Kvartal=Quarter

Måned=Month

I have a mastercalendar including YTDifjor (YTDlastyear) and YTD:

(Year(TempDate)=Year(Today()) and TempDate<= \$(varToday), 1) as YTD,

if(Year(TempDate)=Year(Today())-1 and TempDate<= AddYears(\$(varToday), -1), 1) as YTDifjor,

I want to calculate the last years figures based on the selections. If I select 2013 and February, I want to calculate last years figures for January and February.

What is the expression for last year?

• Re: YTD based on selections

For January 2012, i.e. 13 months back:

=Sum({< År=,Kvartal=,Måned=,DatoNum={'>=\$(=(MonthStart(Max(DatoNum),-13))<=\$(=MonthStart(Max(DatoNum),-12))'}>} FaktBeløp)

• Re: YTD based on selections

CY YTD =sum({\$<Year = {\$(=max(Year))}, Month= {"<=\$(=max({<Year={\$(=max(Year))}>} Month))"}>}FaktBeløp)

PY YTD=sum({\$<Year = {\$(=max(Year - 1))}, Month= {"<=\$(=max({<Year={\$(=max(Year))}>} Month))"}>} FaktBeløp)

• Re: YTD based on selections

Thanks guys, but I didn't succeed with your expression. However, I made the following one that worked.

• Re: YTD based on selections

Hi Jon,

Can you please translate the below formula into English?