How to calculate the YTD up to the previous month of the previous year

[Aure35]

Hi all,

I want to calculate the YTD sales up to previous month (Current month-1) and to compare them to the same period last year  (current year -1; current month-1).

To calculate the YTD quantity up to previous month, I've found the solution under the following link: https://community.qlik.com/t5/New-to-Qlik-Sense/How-to-calculate-the-YTD-for-previous-month/td-p/135...

Now, I want to calculate the YTD quantity last year, up to previous month. How I calculate that?

Exemple: if my current month is July, then I want to compare the sales between the 2 periods :

- From 01/01 current year to 30/06 current year

This is my YTD Set Analysis statement: =sum({<[D_SALES.autoCalendar.YearMonth]={">=$(=Floor(YearStart(Today(),-1)))<=$(=Floor(MonthEnd(Today(),-1)))"}>}QTE_POSTE)

From 01/01 previous year to 30/06 previous year

What should be the expression in this case?

Many thanks for your help!

Regards,

Aurélie

[ggijben]

Hi @Aure35 , 

You can use the AddYears() and AddMonthts() functions to shift the current period to previous year.

See:

AddMonths() 

AddYears() 

[vinieme12 · Accepted solution]

Use the Date field instead

 =sum({<[D_SALES.autoCalendar.Date]={">=$(=Yearstart(Max([D_SALES.autoCalendar.Date]),-1))<=$(=MonthEnd(AddYears(Max([D_SALES.autoCalendar.Date]),-1),-1))"}>}QTE_POSTE)

[Aure35]

Many thanks @vinieme12 !

In the meantime, making some tests based on @ggijben tip, I came up to the following expression: 

sum({<[D_SALES.autoCalendar.YearMonth]={">=$(=Floor(YearStart(addyears(Today(),-1))))<=$(=Floor(MonthEnd(addyears(Today(),-1),-1)))"}>}QTE_POSTE)

And it works too.

Thanks to both of you.

Aurélie