7 Replies Latest reply on May 29, 2015 6:12 AM by mikkel.refsgaard

    Creating adjustable weights on a dashboard

    mikkel.refsgaard

      Hi all,

       

      Hope someone can help me with a solution.

       

      I have 5 KPIs for each of my IDs. Now I want to add a new variable that weights the KPIs after some percentages I want to be able to adjust in my dashboard.

       

      What I have tried:

       

      1) Created 1 parameter for each KPI allowing for values between 0% and 100%

      2) Created a calculated field: KPI_1 * Parameter_weight_1 + ... + KPI_5 * Parameter_weight_5

      3) Made my parameters appear on my dashboard in order to be able to adjust them

       

      The problem seems to be that parameters cannot be used this way in calculated fields as my new variable only displays nulls.

       

      Does anybody have a clever way to make adjustable weights on a dashboard?

        • 1. Re: Creating adjustable weights on a dashboard
          Felix Curran

          Hi Mikkel,

           

          You most certainly can use parameters in calculated fields like that.

           

          In the attached workbook I have a parameter that I use to change the value of sales. Have a look and see if that is the basics of what you need.

           

          If you could try explaining what you need to me again, that would be helpful. I wouldn't mind having a crack at this problem, it sounds fun!

           

          Regards,

          Felix

          • 2. Re: Creating adjustable weights on a dashboard
            mikkel.refsgaard

            Hi Felix,

             

            Thank you for your input! I looked at your workbook and that is exactly what I want to do. The problem arises when I include more than one parameter in my calculated field.

            If I create another parameter and include this in my calculated field like this:

             

            [2012 Sales] * ([Percentage] / 100) + [2013 Sales] * ([Permille] / 1000)

             

            Then the outcome for variable 'Param %age' turns blank in the table (see the attached workbook).

             

            Thank you for taken the time to look at it.

            • 3. Re: Creating adjustable weights on a dashboard
              Felix Curran

              Hi Mikkel,

               

              I see what you mean, bringing two parameters together in that way in a calculated field seems to return a blank value. I'm not sure why that is.

               

              However, you can make calculated fields that use the parameters separately and then do additional calculations on the returned values. Like this:

               

              Calc1 = Field * Param1

              Calc2 = Field * Param2

              EndCalc = Calc1 * Calc2

               

              So by doing this you are essentially writing a calculation like:

               

              (Field * Param1) * (Field * Param2)

               

              Honestly, I'm not 100% sure what your overall requirement is. Could you try explaining it in a different way to me?

               

              Regards,

              Felix

              • 4. Re: Creating adjustable weights on a dashboard
                Felix Curran

                Ah Mikkel,

                 

                The reason your calculated field ([2012 Sales] * ([Percentage] / 100) + [2013 Sales] * ([Permille] / 1000)) didn't work is because you multiplied 2012 sales with 2013 sales! Try changing the 2013 sales to 2012 sales and it will work again.

                 

                You could also wrap the 2012 sales and 2013 sales fields in the ZN() function to return 0 instead of nulls.

                 

                Regards,

                Felix

                • 5. Re: Creating adjustable weights on a dashboard
                  mikkel.refsgaard

                  Hi again,

                   

                  You are right it works when I use [Sales 2012] in both expressions. This is however not what I am trying to do. (It doesn't work either to use separate calculated fields when [Sales 2012] is used in one of them and [Sales 2013] is used in another.

                   

                  What I want to create is a dashboard that can show an overall KPI created by 5 different KPIs weighted by a percentage adjustable for the user.

                   

                  I want a table like this:

                   

                  ID KPI1 KPI2 KPI3 KPI4 KPI 5 Weigted_ KPI

                  A     1       2      1       4      1                2.8

                  B

                  C

                  -

                  -

                  The weight that the single KPI should be multiplied with should be adjustable for the user such that if KPI1 is more important in Q1 it is easy for the user to assign a high weight to KPI1 in this quarter in order to get the right overall KPI. Next quarter some other KPI might be more important and it should be easy to adjust the calculation accordingly.

                   

                  Of course the 5 weights should be visible as a slider like in your workbook.

                  Hope it makes sence.

                  • 6. Re: Creating adjustable weights on a dashboard
                    Felix Curran

                    Hi Mikkel,

                     

                    I'm having trouble figuring out how this is used in a real example, but I've attempted to give you a solution.

                     

                    In the workbook attached you can find a sheet with five KPI sliders, ranging from 1-5. There is also a type in KPI Weight % parameter which ranges from 0-1, this is a float to simulate a percentage from 0-100%.

                     

                    The calculated field "Weighted KPI" calculates the average of the five KPIs and then multiplies this value by the KPI Weight %.

                     

                    Once again, I'm not exactly sure what your overall intentions are for this but hopefully what I've given you can be a basis for whatever you need.

                     

                    Regards,

                    Felix

                    • 7. Re: Creating adjustable weights on a dashboard
                      mikkel.refsgaard

                      Thank you for your effort but still not quite what I was looking for. I try to explain it in another way.

                       

                      We have some key control measures (lets say 2 for simplicity). These key controls should identify the most risky customers. key_control_A > 1 indicates low risk and below 1 indicates high risk. The key controls measure different things so we need to combine them to capture the overall risk of the customers.

                       

                      Key_control_A is more important than key_control_B so we need to give A a higher weight than B. However I am not sure what the 'right' weights are and what impact the different weights will have on my customer portfolio e.g. how many will get a final key control measure below 1. Therefore I want to create a tool where I can adjust the weights and quickly see the impact on my portfolio.

                       

                      Therefore the perfect tool would look something like this:

                       

                      Calculated field: Final_key_control_measure = key_control_A * weight_A + key_control_B * weight_B

                      where weight_A and weight_B are adjustable parameters.

                       

                      Hope it makes mre sence now