Percentage Change in tables

alisonpwallis — Mon, 23 Feb 2015 14:29:30 GMT

Dear All

I have a straight table that does some basic calculations. What I am calculating is usually just the sum in each case and I show the subtotals. One of my dimensions is year and the purpose of the table is to compare year on year.

Dept	Proposed Year of Entry	Total	Status 1	Status 2	Status 3	Status 4
		16809	9204	1210	80	225
Dept 1	201516	6223	3378	556	32	82
Dept 1	201415	4736	2419	427	37	34
Dept 1	201314	3919	3800	186	35	35
Dept 2	201516	639	431	14	7	3
Dept 2	201415	919	980	21	2	1
Dept 2	201314	363	335	5	0	1

These are not the real figures.... and they don't add up here!

I want to show percentage change between 201516 and the two previous years. I've tried using 'below' and this works great for comparing one year to the year below BUT I want to compare 201516 and 201314 as well as 201516 and 201415 and I can't get this to work. This is my calculation for the one below which does work. How would I compare to two below? I've tried putting in -2 instead of -1 but this doesn't work.

=(sum({$<[Exclusions]={'0'}>}[FTE])/below(sum({$<[Exclusions]={'0'}>}[FTE])) -1)

I've also tried creating a separate chart which just does the comparisons for each status individually BUT I can't get these to show as percentage change so they come out as 110% and 127% which I don't want.

=sum({$<[Exclusions]={'0'},[Proposed Year of Entry]={'201516'}>}[FTE])/sum({$<[Exclusions]={'0'},[Proposed Year of Entry]={'201415'}>}[FTE])

Any ideas on how to use below differently? Or on how to present percentage change properly in the ?

Thanks

Alison

topic Percentage Change in tables in QlikView

Percentage Change in tables