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

    Creating Multiples

    Kevin Koene

      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!

       

      Thank you in advance for your help!

        • 1. Re: Creating Multiples
          maneesh.gaddam

          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
            Kevin Koene

            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.

             

            Thank you for your help!

            • 3. Re: Creating Multiples
              maneesh.gaddam

              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
                Kevin Koene

                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
                  maneesh.gaddam

                  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
                    Kevin Koene

                    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
                      Kevin Koene

                      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
                        maneesh.gaddam

                        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