Calculating Year on Year % in a straight table

[#1]

Hi,

New to Qlikview. I have a table containing revenue across years and I want to calculate the YOY % change.. see example below

	FY2010	FY2011	FY2012	FY2013	FY2014	FY2015	FY2016
Revenue	20,388,965	26,880,741	23,110,900	11,172,859	13,032,210	12,735,551	12,173,399
% Delta		32%	-14%	-52%	17%	-2%	-4%
Formula	=c3/b3-1

I can get it working reasonable well using the following expression (thanks Gysbert):

Sum({$<Measure={'Revenue}>}Data)/Above (Sum({$<Measure={'Revenue'}>}Data))-1

However there seems to be a couple of limitations with this methodology:

1. If I select a range like FY2010 to  FY2015 no calculation of % delta is provided for FY2010 even though the data is available, just not in the actual table

2. If I select a product that launched in, say FY2012,  then all the NULL previous years are displayed in the table - see below.

All help appreciated - my guess is that I'll need to do this using set analysis

John

[marcus_malinow]

One possible solution to this is to use 'As Of' dates. You would set up years in your AsOf table, with a couple of flags - CYFlag and PYFlag. Then use these in your set analysis.

Here are a couple of useful resources with further information on this:

http://community.qlik.com/docs/DOC-4252

http://community.qlik.com/message/101275#101275

[#3]

John,

If you need often to do computations like that, a simple way is to amend your data model. Because you will get a limitation with Set Analysis (computed once a chart and not by row even if your calculation by year needs a calculation by row) and the functions like above do not take into account what is not in the tanle/graph.

See a doc I publised some time ago:

http://community.qlik.com/docs/DOC-4821

Fabrice