This Year v Last Year Analysis Options

Summary

A view on the current performance of a company compared with last year is a useful indicator.  When we prepare this chart we immediately discover that if we sum(sales) for the years we are comparing all of last year with an incomplete year for the current year.  Often we need to be able to calculate the sales for last year up to the date that corresponds to the current day/month.  There are two possible solutions to this requirement.

Solution 1 – Set Analysis

Pre-requisites:

• Date breakdown in script
  • Year(OrderDate) as OrderYear,
  • DayNumberOfYear(OrderDate) as OrderDayNumberOfYear,
• Variables for current date in script (assuming being loaded each day)
  • let varThisYear = year(today());
  • let varLastYear = (year(today())-1);
  • let varDayNumberOfYear = DayNumberOfYear(today());

Expression to calculate sales for last year up to corresponding day last year:

=Sum ({1< OrderYear = {$(varLastYear)}, OrderDayNumberOfYear= {“<=$(varDayNumberOfYear)”}>}OrderSalesAmount)

This is not an easy calculation to read so an alternative method can be deployed as per Solution 2.

Solution 2 – LYTD Flag

Pre-requisite

• Flag in script to identify records from last year corresponding to today’s date
  • if(year(OrderDate)=(year(today())-1),if(DayNumberOfYear(OrderDate)<=DayNumberOfYear(today()), 1,0)) as LYTD_Flag,

Expression to calculate sales for last year up to corresponding day last year:

=Sum ({1<LYTD_Flag = {1}>}OrderSalesAmount)

Which makes an easier expression to read!