Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
arnould_it
Contributor III
Contributor III

Previous YTD, until the end of current month


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
Labels (2)
1 Solution

Accepted Solutions
arnould_it
Contributor III
Contributor III
Author

This seems work:

Sum({<date={">=$(=date(YearStart(AddYears(Max(date),-1)),'YYYY-MM-DD'))<=$(=date(addmonths(MonthEnd(Today()),-12),'YYYY-MM-DD'))"}>}importo_netto)

Date starts from 01/01/2018 until 30/06/2018

 

View solution in original post

12 Replies
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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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