How to: Sum quantity for last year year to date?

[AnalyticsDev]

Hi, 

I have a requirement to sum quantity for last year(previous year) year to date.  So if today is 5/21/2024, I want to sum quantity for 1/1/2023 through 5/20/2023.

My set analysis expression is as follows:

sum({<status_date={">=$(=YearStart(addYears(max(status_date),-1)))<=$(=addYears(max(status_date),-1))"}>}Quantity)

if I take each portion of that formula, YearStart(addYears(max(status_date),-1)) & addYears(max(status_date),-1)) and drop it into KPI boxes, it returns the proper dates (1/1/203 and 5/20/2023).  When placed in set analysis, its not summing.

Thoughts?

[Sayed_Mannan]

The issue might be with the date range in your set analysis. You're using the same function addYears(max(status_date),-1) for both the start and end of your date range, which will return the same date (i.e., the date exactly one year ago from the maximum status date).

You should modify your set analysis to use `YearStart` for the start of the date range and `YearToday` for the end of the date range. Here's the corrected expression:

sum({<status_date={">=$(=YearStart(addYears(max(status_date),-1)))<=$(=addYears(YearToday(max(status_date)),-1))"}>}Quantity)

This expression will sum the quantity from the start of the previous year to the current date of the previous year. Please replace this in your code and see if it works. If it still doesn't work, there might be an issue with the data type of your status_date field.
Ensure that it's in a date format that Qlik Sense can recognize. If it's not, you might need to convert it using the Date() function.

Let me know if this helps!

[hic]

If you use the auto-calendar, you probably have the following defined:

If($1-YearStart($1) <= Today()-YearStart(Today()), 1, 0) as [InYTD] ,

If you have Year as dimension, you can use this for your set analysis, for example:

Sum({<date.autocalendar.InYTD={1}>} quantity)

If you don't have year as dimension, you can simply add this in the set expression, e.g.

Sum({<year={2023}, date.autocalendar.InYTD={1}>} quantity)

[AnalyticsDev]

Hi Sayed,

Thank you... using your suggestion (modified a bit because otherwise it was still way off) gets me 95% of the way there...in addition to making sure the date format is proper.

This is what I have now:

=sum({<StatusYear = , Month = , status_date={">=$(=YearStart(addYears(max(status_date),-1)))<=$(=addyears(MonthEnd(max(status_date)),-1))"}>}Quantity)

This returns the sum of quantity 1/1/2023 through 4/30/2023.  Its not including the current month of last year (May).  Again, addyears(MonthEnd(max(status_date)),-1) returns 5/31/2023...but the expression is seeing it as 4/30/2023 I suppose.

[Sayed_Mannan]

Try this,

=sum({<StatusYear = , Month = , status_date={">=$(=YearStart(addYears(max(status_date),-1)))<=$(=addmonths(MonthEnd(max(status_date)),-11))"}>}Quantity)

In this expression, `addmonths(MonthEnd(max(status_date)),-11)` is used instead of `addyears(MonthEnd(max(status_date)),-1)`.

This should return the end of the current month of the previous year, thus including the current month.
Please try this and let me know if it works for you. If not, we can explore other solutions.