Previous YTD, until the end of current month

[arnould_it]

Hello,

I have to calculate the sum of sales based on the YTD for the PREVIOUS YEAR, but not until today: I have to consider the last day of the current month for the previous year.

Example: Today is 05/06/2019 -> so I have to sum up the sales starting from 01/01/2018 until 30/06/2018.

What is the best way to do it?

Thanks in advance

Alessandro

[OmarBenSalem]

Here how u should think it:

ur date condition should be sthing like:

Date={>=01/01/2018<=30/06/2018}

We then must find a way to calculate these 2 dates based on ur selection:

ur selection of date would be : Max(Date) why?; if u don't select any date, max date would be ur last available date

if u do select a date ; max(Date) returns ur selected date.

Now, 01/01/2018 is the FIRST day of LAST YEAR:

let's say, w've selected : 04/06/2019

Max(Date) = 04/06/2019

to go back one year before : AddYears(Max(Date),-1) : 04/06/2018

to return the First day of the year? YearStart(04/06/2018) = YearStart(AddYears(Max(Date),-1)) = 01/01/2018

Now for the 30/06/2019 part : this is the Last day of the month of the selected Date (max(Date))

=> MonthEnd function:

MonthEnd(Max(Date)) = 30/06/2019

to return one year before :

AddYears(MonthEnd(Max(Date)),-1) : 30/06/2018

ur expression should then be :

sum({<Date={">=$(=YearStart(AddYears(Max(Date),-1)))<=$(=AddYears(MonthEnd(Max(Date)),-1) )"}>}Sales)

but this is not complete yet, u should break the relation between qlik and other date fields u have

sum({<Year,Month,Week, Date={">=$(=YearStart(AddYears(Max(Date),-1)))<=$(=AddYears(MonthEnd(Max(Date)),-1) )"}>}Sales)

Why?

Suppose u've selected not a date but a Year :2019

so max(Date) would return the LAST DATE U HAVE in ur data in the Year 2019

Let's say it's 05/06/2019

so, "logically, our expression could return sales from 01/01/2018 to 30/06/2018 right?

NO : 

it'll return NOTHING because when u select Year =2019 : Qlik would SEE only the YEAR 2019 while u're trying to return data in 2018..

So, wht we do is Add the Year,Month,Week, part into our expression to tell Qlik to continue to see ALL years,months weeks even if we select a Year..

Hope that was clear enough..

Ps: this might help u :

https://community.qlik.com/t5/New-to-Qlik-Sense/YTD-MTD-issue/td-p/1278297

[arnould_it]

Hi Omar,

thanks for your reply and suggestion.

I've tried to implement it but the "date end" returned is 31/12/2018 and NOT 30/06/2018, it seems Qlik doesn't consider the end of the current month for the previous year.

Maybe it's needed to introduce the "Today()" function into the Set Analysis?

This is the formula, applied as you suggested:

sum({<Year=,Month=,Week=, date={">=$(=YearStart(AddYears(Max(date),-1)))<=$(=AddYears(MonthEnd(Max(date)),-1) )"}>}importo_netto)

[OmarBenSalem]

Please create 2 kpi objects 

1)date( MonthEnd(Max(date)))

2)date( AddYears(MonthEnd(Max(date)),-1))

tell me what these return

[arnould_it]

1)  date(MonthEnd(Max(date)))                             --> 31/12/2019

2)  date(AddYears(MonthEnd(Max(date)),-1))  --> 31/12/2018

I'm using Qlik Sense, last version.

[OmarBenSalem]

select 05/06/2019, what does it return in this case?

[arnould_it]

The result has to be independent from any date selection, I've tried this:

date(addmonths(MonthEnd(Today()),-12))      it returns 30/06/2018

Could be right?

[OmarBenSalem]

Independant of any selection?

if u select 04/03/2018 for example?

u don't want to it to return :

01/01/2017 to 31/03/2017?

if not use today, if u want to recalculate the limits, use max5date) as seen 

[arnould_it]

YTD (YearToDate), MTD(MonthToDate) etc. have to be independent from any year, month, week and day selection.

They have always to start from the first day of the current year to the current day of the current year, comparing this year with the previous for the same range.

Example: YTD (from 01/01/2019 to 04/06/2019) - YTD Previous Year: (from 01/01/2018 to 04/06/2018).

But, in this case, my customer need to see the YTD of previous year not just until the current date of the previous year, but until the last day of the current month for the previous year.

More, he wants to see all the current month of the last year, always independently from any selection you could apply (another formula).

[arnould_it]

More briefly, I need to implement:

YTD Previous Year, starting from the first day of the previous year until the last day of current month (previous year) ; so: from 01/01/2018 to 30/06/2018)

MTD Previous year, all month: starting from 01/06/2018 to 30/06/2018