topic Percentage Change Ranking in QlikView

Percentage Change Ranking

alisonpwallis — Tue, 31 Mar 2015 13:08:23 GMT

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

Re: Percentage Change Ranking

pokassov — Tue, 31 Mar 2015 13:28:21 GMT

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

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.

Re: Percentage Change Ranking

luciancotea — Tue, 31 Mar 2015 13:29:51 GMT

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.

Re: Percentage Change Ranking

alisonpwallis — Wed, 01 Apr 2015 13:21:00 GMT

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

Re: Percentage Change Ranking

alisonpwallis — Wed, 01 Apr 2015 13:21:55 GMT

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