Percentage Change Ranking

[alisonpwallis]

Dear All

I have a table with values for different countries and years like this:

Country     year          No

England     201516     5000

England      201415     4500

England     201314     4000

India           201516     300

India          201415     250

India          201314     275

I want to show the percentage change between the years (which I have done using the below calculation (with some set analysis)

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

What I want to be able to do is sort the table so that the country with the highest percentage change is at the top. How do I do this?

Thanks

Alison

[pokassov]

Hi!

Can you change the script?

t1:

load * Inline [

Country,year,No

England,201516,5000

England,201415,4500

England,201314,4000

India,201516,300

India,201415,250

India,201314,275

];

t2:

LOAD

  *,

  if(Country=Previous(Country),

  round(No/Previous(No)-1,0.001),0) as Dif

Resident

  t1

Order By

  Country,

  year

;

DROP Table

  t1;

Tnen you can create straight table with expression Dif.

You can hide it or show. Also you can use it in sorting tab.

[luciancotea]

You want to sort, but the calculation logic is based on calendar sort. If set analysis would evaluate the conditions for each row, you might stand a chance.

So, your option is to bring previous year value on the same row with the current year and calculate the percentage.

[alisonpwallis]

Unfortunately, I can't change the load since the totals for each country are a sum - I have a record for each person and I sum the total for each country in the chart.

However, your suggestion sounds really useful anyway for other things. I will make a note of it and use it for something else!

Thanks for your help

[alisonpwallis]

I tried this but ended up with a percentage rather than percentage change (so I got 107% rather than 7%). I've asked the users and compromised on sorting the table by the country with the highest number rather than the highest percentage change - using an expression in the custom sort.

Thanks for your help!

Alison