topic Re: YTD based on selections in QlikView

YTD based on selections

Wed, 23 Oct 2013 15:37:05 GMT

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

Gysbert_Wassenaar — Wed, 23 Oct 2013 16:24:09 GMT

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

preminqlik — Wed, 23 Oct 2013 16:29:23 GMT

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

Thu, 24 Oct 2013 12:56:12 GMT

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

Sum=({<År=,Måned=,MånedÅr=,Dag=,Dato={">=$(=StartdatoMnd(addmonths(max(Dato),-12))) <=$(=addmonths(Max(Dato),-12))"}>} FaktBeløp)

Re: YTD based on selections

Fri, 14 Feb 2014 06:59:13 GMT

Hi Jon,

Can you please translate the below formula into English?

Sum=({<År=,Måned=,MånedÅr=,Dag=,Dato={">=$(=StartdatoMnd(addmonths(max(Dato),-12))) <=$(=addmonths(Max(Dato),-12))"}>} FaktBeløp)

Thanks in advance,

Soumya