8 Replies Latest reply on Sep 25, 2018 9:05 AM by maneesh.gaddam

# Creating Multiples

Hi everyone,

I'm trying to create something that is comparable to industry multiples that are used for business valuations and such. However, I'm working with UN population data so my version of those multiples would look something like this: The total population of *insert country* in 2050 will be X times the size of the total population of *insert different country* in 2050 (or in other words: total population of country X in 2050 divided by total population of country Y in 2050). I could very easily do those calculations in Excel, Stata etc. but I am using an extended dataset for work and would like to create a one-stop-shop for my policy colleagues. Therefore, I would like to create an option where I can choose the year and two countries (most likely in the form of parameter controls) that I would like to compare and the 'multiple' as an output.

The setup of the attached dataset is purposely very simple and thus only includes 2 dimensions (year and country) as well as one measure (total population). I've been playing around with various combinations of calc. fields and parameters but couldn't figure it out.

I hope it is somewhat clear what my problem is and what I would like to achieve. If not, please do let me know!

• ###### 1. Re: Creating Multiples

Kevin,

You have to create two parameters, one for year and one for city and use them in your calculation. I'm on 10.5 version, sou couldn't open your workbook. If you could export your workbook to 10.5 version, I can take a look at it.

• ###### 2. Re: Creating Multiples

Hi Maneesh,

Thank you for your answer. For my calculations, I would need 4 parameters, which I have had no issue to create. I think I need to clarify, sorry about that! I also attached a 10.5 version of the workbook.

Year Parameter 1 and Country Parameter 1 = Output Country 1

Year Parameter 2 and Country Parameter 2 = Output Country 2

The next step would be to divide output 1 / output 2 and this is where my issue arises as I haven't been able to figure out a way in which my calc. field takes the output values from the parameters. So far it only takes the parameter names but the calculated numerical values. Hope this helps.

• ###### 3. Re: Creating Multiples

Kevin,

I created one parameter for Year as I assumed year would be same for X and Y countries. If you want it to be different selections, create a second parameter and apply it in the Y population calculated field.

Having said that, please find the attached workbook and let me know if you have any questions.

1 of 1 people found this helpful
• ###### 4. Re: Creating Multiples

Hi Maneesh,

Thank you again for your help and my apologies for the late reply.

What you've done is exactly what I was going for. However, I am unable to replicate your work into my original dataset. I've attached a new version of my workbook, which includes the parameters and calc fields that I used. Would you be able to help me spot my error? I think it might possibly be due to the parameters being lists (which I need to use that way) instead of a fill in.

I'm very grateful for your help!

• ###### 5. Re: Creating Multiples

Kevin,

Change your Country pick measures as follow

SUM(IF [Country] = [Country Parameter 1]

AND YEAR([Year]) = YEAR([Year Parameter 1])

THEN [Total population (thousands)] END)

In my code, I used the Year parameter as integer so I didn't convert the date to int. But, you have year parameter as data and adding INT on top of it, wouldn't directly convert it to date. So, add YEAR([Year Parameter 1]) which picks up the year of your parameter selection.

1 of 1 people found this helpful
• ###### 6. Re: Creating Multiples

Thank you for your help. I haven't made use of the INT and YEAR functions in my own attempts but this clarifies a lot!

• ###### 7. Re: Creating Multiples

Hi Maneesh,

I do have one last question. Would it be possible to insert another parameter, which would allow me to select the measure that would be the output of the calculated field?

So instead of Total Population in below equation, I would like to have the option to choose Median Age without having to change the calc field.

SUM(IF [Country] = [Country Parameter 1]

AND YEAR([Year]) = YEAR([Year Parameter 1])

THEN [Total population (thousands)] END)

Thank you so much for your help!

• ###### 8. Re: Creating Multiples

Yes, you can. Create a String parameter with list saying Median, Total Population and any other calculations you might want to set.

Then create a calculated field referencing this parameter. I attached a workbook, take a look and let me know if you have any questions.

1 of 1 people found this helpful