Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to create an expression that summarizes the previous year population of each country without having to manually enter the year (i.e. 2017) at the beginning of each new year.
I got this far but I need help
Sum({$<[Country]={Germany}>}[Population])
Pot 1 | Year | Population |
Russia | 1/1/2018 | 143,964,709 |
Russia | 1/1/2017 | 143,989,754 |
Russia | 1/1/2016 | 143,964,513 |
Germany | 1/1/2018 | 82,293,457 |
Germany | 1/1/2017 | 82,114,224 |
Germany | 1/1/2016 | 81,914,672 |
Brazil | 1/1/2018 | 210,867,954 |
Brazil | 1/1/2017 | 209,288,278 |
Brazil | 1/1/2016 | 207,652,865 |
Portugal | 1/1/2018 | 10,291,196 |
Portugal | 1/1/2017 | 10,329,506 |
Portugal | 1/1/2016 | 10,371,627 |
Argentina | 1/1/2018 | 44,688,864 |
Argentina | 1/1/2017 | 44,271,041 |
Argentina | 1/1/2016 | 43,847,430 |
Belgium | 1/1/2018 | 11,498,519 |
Belgium | 1/1/2017 | 11,429,336 |
Belgium | 1/1/2016 | 11,358,379 |
Poland | 1/1/2018 | 38,104,832 |
Poland | 1/1/2017 | 38,170,712 |
Poland | 1/1/2016 | 38,224,410 |
France | 1/1/2018 | 65,233,271 |
France | 1/1/2017 | 64,979,548 |
France | 1/1/2016 | 64,720,690 |
With your data, i did the following
load *,
Year(Year) as Num_Year;
load * Inline
[
Pot 1,Year,Population
Russia,1/1/2018,143964709
Russia,1/1/2017,143989754
Russia,1/1/2016,143964513
Germany,1/1/2018,82293457
Germany,1/1/2017,82114224
Germany,1/1/2016,81914672
Brazil,1/1/2018,210867954
Brazil,1/1/2017,209288278
Brazil,1/1/2016,207652865
Portugal,1/1/2018,10291196
Portugal,1/1/2017,11329506
Portugal,1/1/2016,12371627
Argentina,1/1/2018,44688864
Argentina,1/1/2017,44271041
Argentina,1/1/2016,43847430
Belgium,1/1/2018,11498519
Belgium,1/1/2017,11429336
Belgium,1/1/2016,11358379
Poland,1/1/2018,38104832
Poland,1/1/2017,38170712
Poland,1/1/2016,38224410
France,1/1/2018,65233271
France,1/1/2017,64979548
France,1/1/2016,64720690
];
And with the added field, I used it to get the current year for all selections and subtract one to get the last year.
sum
(
{<
Num_Year={"$(=Max(Total Num_Year)-1)"}
>}
Population
)
Getting:
With your data, i did the following
load *,
Year(Year) as Num_Year;
load * Inline
[
Pot 1,Year,Population
Russia,1/1/2018,143964709
Russia,1/1/2017,143989754
Russia,1/1/2016,143964513
Germany,1/1/2018,82293457
Germany,1/1/2017,82114224
Germany,1/1/2016,81914672
Brazil,1/1/2018,210867954
Brazil,1/1/2017,209288278
Brazil,1/1/2016,207652865
Portugal,1/1/2018,10291196
Portugal,1/1/2017,11329506
Portugal,1/1/2016,12371627
Argentina,1/1/2018,44688864
Argentina,1/1/2017,44271041
Argentina,1/1/2016,43847430
Belgium,1/1/2018,11498519
Belgium,1/1/2017,11429336
Belgium,1/1/2016,11358379
Poland,1/1/2018,38104832
Poland,1/1/2017,38170712
Poland,1/1/2016,38224410
France,1/1/2018,65233271
France,1/1/2017,64979548
France,1/1/2016,64720690
];
And with the added field, I used it to get the current year for all selections and subtract one to get the last year.
sum
(
{<
Num_Year={"$(=Max(Total Num_Year)-1)"}
>}
Population
)
Getting: