Simply create YTD, moving totals and comparisons versus Year Ago


    When we want to do something with QlikView, we face so many possibilities to achive the goal. And sometimes, the first idea we have is not necessarily the simplest one.

    I wanted to do create YTD and moving totals measures, difference (absolute and in percentage) of these moving totals versus the Year ago. QlikView is a BI tool: no problem I thought. Well, it was not so simple ...   The expressions became so complex that I finally did not understand them any more!

     

    After some searches and many tries,the solution to such a chart is very simple. You just need to look at the right place.

    As you can see, this chart contains actual data and the one of the year ago, a YTD starting in January and the same for the Year Ago, Rolling 3 and 12 periods and the same for the Year Ago. These values are contained in a TIME ANALYSIS axis that the user can select in a simple List Box to display only YTD and Rolling12 for example:

    MovingTotals.JPG

    The document will help you to understand the modification you have to do to your data model to make this report very simple. As you can already see, the expression is the simplest expression in QlikView: sum(VALUE).

     

    This simplicity let you afterwards mix the different measures of the database with this TIME ANALYSIS axis : get the current sales values, but the share the products represents the last 12 motnhs, and a difference vs year ago:

    MovingTotals2.JPG

     

    Many thanks to G. Wassenaar and his post that helped me to understand where to look exactly :  http://community.qlik.com/docs/DOC-4252

     

    Have a nice reading

     

    Fabrice AUNEZ

     

    I have attached sample QVW and Sample data. Hope this will be helpful for all..