This Year v Last Year Analysis Options

Qlik Technical Article

Article sections

    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:
    in QlikviewSupportTroubleshooting
    Share This Post
    More To Explore

    Sign Up To Our Newsletter For Regular Updates And News