Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Jonathan_Alm
Partner - Creator
Partner - Creator

YTD compared to LYTD

Hi,

I am having an issue with YTD compared with LYTD. 

In my example I am loading the application at during the night to catch yesterdays sales.
Let's say that I load the app at 2023-01-02 01:00. 

I will then have sales data from 2022-01-01 up until 2023-01-01 (no sales where made after 00:00 on the 2nd of January)

Date Sales
2022-01-01 100
2022-01-02 120
2022-01-03 150
... ...
2023-01-01 200


I have an autocalendar that helps out with YTD and LYTD, it looks like this:

If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,
Year(Today())-Year($1) AS [YearsAgo] ,

The formula for cathing YTD in set analysis is:

 

Sum({<[Datum.autoCalendar.InYTD]={"1"}, [Datum.autoCalendar.YearsAgo]={"0"}>}Sales)

 

 
But If I want to compare to LYTD I will get the sales for 2022-01-02 too. Since I have not sold anything for this year (2022-01-02) the comparison will be wrong:

 

Sum({<[Datum.autoCalendar.InYTD]={"1"}, [Datum.autoCalendar.YearsAgo]={"1"}>}Sales)

 

 

The result of these  two expressions in a table:

Date InYTD YearsAgo Sales YTD Sales LYTD
2022-01-01 1 1 0 100
2022-01-02 1 1 0 120
2023-01-01 1 0 200 0



What I am looking for is to include everything from last year up until today-1 year -1 date. 
I have tried with the following set analysis but only gives me last years sales for a particular date.

 

Sum({<[Datum.autoCalendar.InYTD]={"1"}, [Datum.autoCalendar.YearsAgo]={"1"}, [Datum.autoCalendar.Date] = {"$(=AddYears(Max([Datum.autoCalendar.Date]), -1))"} >}Sales)

 

 

 

Labels (1)
1 Solution

Accepted Solutions
vinieme12
Champion III
Champion III

1)You can either ditch the YTD flag as below

YTD

= sum({<Date={">=$(=YearStart(Max(Date)))<=$(=Max(Date))"}>}Sales)

LYTD

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

 

2)OR Create flags using the inYearToDate() function

InYearToDate(Date,Today()) as InYTD

InYearToDate(Date,Today(),-1) as InLYTD

then in Charts use

YTD = sum({<InYTD={"1"}>}Sales)

LYTD = sum({<InLYTD={"1"}>}Sales)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

2 Replies
vinieme12
Champion III
Champion III

1)You can either ditch the YTD flag as below

YTD

= sum({<Date={">=$(=YearStart(Max(Date)))<=$(=Max(Date))"}>}Sales)

LYTD

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

 

2)OR Create flags using the inYearToDate() function

InYearToDate(Date,Today()) as InYTD

InYearToDate(Date,Today(),-1) as InLYTD

then in Charts use

YTD = sum({<InYTD={"1"}>}Sales)

LYTD = sum({<InLYTD={"1"}>}Sales)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Jonathan_Alm
Partner - Creator
Partner - Creator
Author

Great advice. All seems like viable options.

 

I also found out that I can make a flag from :

If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,

by setting a -1 after the Today making it:

If (DayNumberOfYear($1) <= DayNumberOfYear(Today()-1), 1, 0) AS [InYTD-1] ,

 

Thanks!

Kind regards, Jonathan