topic Re: YTD compared to LYTD in App Development

YTD compared to LYTD

Jonathan_Alm — Mon, 02 Jan 2023 14:02:41 GMT

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)

Re: YTD compared to LYTD

vinieme12 — Tue, 03 Jan 2023 07:05:52 GMT

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)

Re: YTD compared to LYTD

Jonathan_Alm — Wed, 04 Jan 2023 07:34:52 GMT

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