Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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
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