I have a dataset with file country, state, and population. Below is in example dataset.
The end user is allowed to select only the state field. I want to calculate the total population of a country corresponding to states.
For example If the user selects ON. I want to calculate the total population of Canada. If the user selects NY then the total population of USA.
I created a calculated field like this:country_st = ifelse({state_nm} = ${state}, country, NULL) this is giving me two value, country and null. and then did the sumIf({population}, {country} = country_st. But this is giving me total population of all the countries.
@ErikG I tried sumOver(sum({population}),[{country}],PRE_AGG) But got “For calculation levels PRE_FILTER and PRE_AGG, the operands can’t be aggregated”.
This is not the only calculation I want to do. There are few more calculation I would be doing based on country name. Basically What I need is to get the country name in the calculated field corresponds to whatever input user provide for state. The ifelse statement ‘country_st = ifelse({state_nm} = ${state}, country, NULL)’ giving me 2 output one is country name and whatever is in else condition i.e null or ’ '.
I only need the country name string as a calculated field ‘filter_country’ and i can use it into all other calculated field with ${country} = ${filter_country}
NO this is not what i am looking for. Basically What I need is to get the country name in the calculated field corresponds to whatever input user provide for state. The ifelse statement ‘country_st = ifelse({state_nm} = ${state}, country, NULL)’ giving me 2 output one is country name and whatever is in else condition i.e null or ’ '.
I only need the country name string as a calculated field ‘filter_country’ and i can use it into all other calculated field with ${country} = ${filter_country}
So you want a parameter control for state. Where the user can select a state and based on the selection you want a field that contains the corresponding country.
e.g. parameter is NY the calculated field should show USA?
And you still want to show the total for USA?
@ErikG Thanks! Yes, This is what I am thinking of implementing! But I have the data for all the countries(180) around the world and their states. I just shown sample data with two country for simplicity.
Is there a way to create a calculated field which can fetch the column value of the country corresponds to the control parameter ‘state’, So that I dont need to manually create a list and country as you mentioned: Ifelse(in(state_parameter,[“NY”,“CI”,“FL”,“PEN”]),“USA”,“Canada”).
Maybe it can work with a cascading filter and parameter.
State filter and country filter with parameter (relevant values on state) then you will get only one value for the parameter. In the calculated field you could compare with the parameter. But I’m not 100% sure it will work. I have to check as well.
Was @ErikG 's suggestion above helpful, and if so could you mark their comment as a solution to help the community? It has been awhile since we have heard from you but would still like to help you find a solution.
If we do not heard from you in the next 3 business days this post will be archived.
