Summary: This article provides two options for calculating This Year v Last Year Analysis with QlikView
Article Type: Information / Troubleshooting / Support / Issue Resolution
Related Product(s): This article relates to the following products:

  • QlikView

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

Solution 1

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

Solution 1
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!

Related Information: