11 Replies Latest reply: Oct 10, 2018 12:06 PM by Daniel Berkes

# Current YTD vs Previous whole year showing together

Hi,

I have a line chart where I would like to display the current YTD and the previous full year.

The prev full year should be displayed with the current year values together e.g from JAN 2018 till DEC 2018 and not from JAN 2017 till DEC 2017.

With this, the prev vs curr can be compared easily.

I was able to solve it, with above function i shifted the previous year's value but when I do selection I lose prev year values.

Which make sense since Qlik Sense ( ) evaluate the prev year expression in the selected dims (2018) .

could you suggest solution which can be applied in frontend?

Thanks,

Daniel

• ###### Re: Current YTD vs Previous whole year showing together

Hi,

I would use the dimension of Month and Year as two dimensions, so the year will be applied for the name of the lines. That way you get no issue with showing 2017 and 2018 on the same axis and can apply a simple set-analysis on the expression instead.

• ###### Re: Current YTD vs Previous whole year showing together

Daniel,

Following Expression will work in Case we are having data till date

Sum({<Year={">=\$(=Max(Year-1))<=\$(=Max(Year))"}>}Sales)

In Case, if we are having forecast data.

We need to modify expression

I hope, It will work for you.

• ###### Re: Current YTD vs Previous whole year showing together

Hi Amit, i need two lines one line is showing current ytd and the second line is previus full year ( i do used set analysies) BUT the previous full year should be displayed from 2018.01 - 2018. 12. I used above in the previius expression. But when i selected on chart the previous lost.

• ###### Re: Current YTD vs Previous whole year showing together

Let's assume u have this data:

Load * Inline [

Year,Month,Measure

2018,01,100

2018,02,150

2018,03,120

2018,04,140

2018,05,100

2018,06,200

2018,07,220

2018,08,150

2018,09,170

2018,10,180

2018,11,100

2018,12,190

2017,01,150

2017,02,180

2017,03,200

2017,04,300

2017,05,180

2017,06,110

2017,07,90

2017,08,160

2017,09,150

2017,10,200

2017,11,300

2017,12,190

];

as dim : Month (without Year), only months

Measures:

1)Sum({<Year={"\$(=max(Year))"},Month={"<=\$(=max(Month))"}>}Measure) Label it : Max(Year)

2)Sum({<Year={"\$(=max(Year)-1)"},Month>}Measure) Label it : Max(Year)-1

result:

If u have a date field; change ur measures as follow:

1)Sum({<Month, Year={"\$(=max(Year))"},Date={"<=\$(=max(Date))"}>}Measure) Label it : Max(Year)

2)Sum({<Year={"\$(=max(Year)-1)"},Month,Date>}Measure) Label it : Max(Year)-1

• ###### Re: Current YTD vs Previous whole year showing together

Hi with just month dimension it worked for me as well, but i need Year Month.

• ###### Re: Current YTD vs Previous whole year showing together

Have u tried it? It works no matter the dimension.

• ###### Re: Current YTD vs Previous whole year showing together

I would like to present the previous year's data in the current year.  from 2018 Jan - 2018 Dec

• ###### Re: Current YTD vs Previous whole year showing together

Are u being serious? Is this what u're aiming for, it's INCORRECT.

Good luck

• ###### Re: Current YTD vs Previous whole year showing together

yes, since we call it reference not previous. It makes sense comparing the actual to a reference (which is the previous)

• ###### Re: Current YTD vs Previous whole year showing together

Hi Deniel,

What you are looking for is comparision as ref to previous year.

If you will create chart by using YEAR_Month Dim, it will return you 22 Point (from Jan 2017 to Oct -2018) on X-scale, this will never give you expected result (Comparision CY to PY)

Exp:

--->>

You can only use Month as Dimension Then only you can compare with previous year. For distinguish Measure, Use different color for measure and USE LEGEND.

Expression will be

PY -> Sum({<Year={">=\$(=Max(Year-1))"}>}Sales)

CY -> Sum({<Year={">=\$(=Max(Year))"}>}Sales)

Or anyother expression from above suggested by others, all will work accordingly.

I hope, It will work for you

• ###### Re: Current YTD vs Previous whole year showing together

Hi,

no it wont work.

I was able to do those.

However the requirement is again different.

I can achieve at backend, but I wanted to do it at fronted.

Dan

• ###### Re: Current YTD vs Previous whole year showing together

Can you explain in detail, what exactly you are doing in Backend? Any Code or Logic?. Please share.

So that, I can figure it out.

Thanks,

Amit

• ###### Re: Current YTD vs Previous whole year showing together

This is a POC (proof of concept) solution for this problem, which was written quickly but solve the issue. The key thing is the CalendarMixing (suggested by one of my colleague) table.

MainData:

date(today() - IterNo()) as Date,

round(rand()*100) as Value

AutoGenerate 1

While 10000 - IterNo() > 0;

MinMax:

min(Date) as minDate,

max(Date) as maxDate

resident MainData;

MinMaxWeek:

WeekStart(Date) as WeekStart

resident MainData;

let vminDate = Peek('minDate',0,'MinMax');

let vmaxDate = Peek('maxDate',0,'MinMax');

drop table MinMax;

CalendarFlag:

tempDate,

//FLAGS

If(tempDate>=AddMonths(today(),-2,1) and tempDate<=today(),1,0) as CURRENT_3_MONTH,

InYear(tempDate, today(),0) *-1 as CURRENT_YEAR,

InYear(tempDate, today(),-1) *-1 as LAST_YEAR,

if(tempDate>if(InWeek(tempDate,\$(vmaxDate),-3),tempDate),tempDate,null()) as WeekFlag

//     If(tempDate>=(if(InWeek(tempDate,\$(vmaxDate),-3),tempDate))  and

//         tempDate<=(if(InWeek(tempDate,\$(vmaxDate),0),tempDate)),tempDate)

//         as WeekFlag

;

\$(vminDate) + IterNo() as tempDate

AutoGenerate 1

while \$(vminDate) + IterNo() <= \$(vmaxDate);

CalendarMixing:

NoConcatenate

tempDate as Date,

if(CURRENT_YEAR = 1

,date(tempDate)

if(CURRENT_YEAR=1,1,2) as CURRENT_LAST_FLAG

Resident CalendarFlag

where LAST_YEAR + CURRENT_YEAR =1;

drop table MinMaxWeek;

With filter applied it is working:

Without filter: