Skip to main content
Announcements
Document boards are being consolidated, this board no longer allows NEW documents READ MORE

Simply create YTD, moving totals and comparisons versus Year Ago

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Simply create YTD, moving totals and comparisons versus Year Ago

Attachments


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..
Comments
vikasmahajan

Great Article !!!!!!!!

0 Likes
Not applicable

Good Work Fabrice...
This is of great help..

0 Likes
selcukcadir
Creator II
Creator II

Could you share the document?

Thanks.

0 Likes
MK_QSL
MVP
MVP

Good Work Done !!

Better if you upload .qvw also.

Not applicable

Hi,

What do you mean by "sharing the document" ?

All the scripts are in the PDF file.

Fabrice

0 Likes
selcukcadir
Creator II
Creator II

Hi,

example qvw document .

thank you for great post.

0 Likes
Not applicable

Great Article

0 Likes
bbi_mba_76
Partner - Specialist
Partner - Specialist

Great!!

0 Likes
Not applicable

Nice Article

0 Likes
Not applicable

great!

0 Likes
Version history
Last update:
‎2013-09-17 05:15 AM
Updated by: