4 Replies Latest reply: Nov 12, 2012 1:13 PM by Rob Wright RSS

    Part 2 - Presenting chart bars as 100% value

      Hi, I have the same issue with another chart but this time it has 2 dimensions and an IF statement to calculate the result.

       

      I'm struggling to create the same result as my previous question and present the information in a 100% value.

       

      This is the current Expression

       

      =if(Response_Level_2='Office',(c2_office_perc/1)*sum ({$<Source_Key={3}>}Ans_Percent)/(c2_office_split_perc_sum),sum ({$<Source_Key={2}>}Ans_Percent)/(c2_loc_split_sum_perc))

       

      It take the information from Source Key 3 which is a breakdown of results for Office (calculating the results as a percentage for time spent at the "Desk","Open Space" and "Room") and also the data from Source Key 2 to provide the results for "Home" and "Out of Office".

       

      The Chart it produces the following chart

       

      Div Chart 3.png

      Based on the previous question that was answered by whiteline - correct answer below for single dimension.

       

      =Sum({$<Source_Key={1}>}  Ans_Percent)/Sum({$<Source_Key={1}>} TOTAL<Division_Name> Ans_Percent)

       

      How would I make the expression above work with this suggestion.

       

      Sorry about this but I have tried all sorts of options and configurations but I keep getting an error.

       

      Any help you can provide again is much appreciated.

       

      Thanks

      Rob

        • Re: Part 2 - Presenting chart bars as 100% value
          whiteline _

          Hi again.

           

          What are the statements marked bold in your expression ?:

          =if(Response_Level_2='Office',(c2_office_perc/1)*sum ({$<Source_Key={3}>}Ans_Percent)/(c2_office_split_perc_sum),sum ({$<Source_Key={2}>}Ans_Percent)/(c2_loc_split_sum_perc))

           

          And what dimension do you have now in addition to Devision_Name?

            • Re: Part 2 - Presenting chart bars as 100% value

              Hi the statements are variables below

               

              c2_office_perc


              =sum ({$<Source_Key={2},Response_Level_2={'Office'}>}Ans_Percent)/c2_loc_split_sum_perc


              c2_office_split_perc_sum


              =sum ({$<Source_Key={3}>}Ans_Percent)

               


              c2_loc_split_sum_perc


              =sum ({$<Source_Key={2}>}Ans_Percent)

               

              It might help if I add the qvw?

               

              Thanks

              Rob

                • Re: Part 2 - Presenting chart bars as 100% value
                  whiteline _

                  Again, why do you use the variables in this way ?

                   

                  Anyway, the whole your expression is:

                   

                  =if(Response_Level_2='Office',

                  sum ({$<Source_Key={2},Response_Level_2={'Office'}>}Ans_Percent)

                  /sum ({$<Source_Key={2}>}Ans_Percent)

                  *sum ({$<Source_Key={3}>}Ans_Percent)

                  /sum ({$<Source_Key={3}>}Ans_Percent)

                  ,

                  sum ({$<Source_Key={2}>}Ans_Percent)/sum ({$<Source_Key={2}>}Ans_Percent))

                   

                  I think you could correct the ELSE case (bolded) by your self as it's almost equal to the discussed previously.

                  As for the first part of IF expresison, choose what sum is the right denominator and use total qualifier correspondingly.

                    • Re: Part 2 - Presenting chart bars as 100% value

                      Well that was journey moment!!!!

                       

                      Here is the final expression

                       

                      if(Response_Level_2='Office',(sum ({$<Source_Key={2},

                      Response_Level_2={'Office'}>}TOTAL<Division_Name,Department_Name> Ans_Percent)

                      /sum ({$<Source_Key={2}>}TOTAL<Division_Name,Department_Name> Ans_Percent)/1)

                      *sum ({$<Source_Key={3}>}Ans_Percent)

                      /sum ({$<Source_Key={3}>}TOTAL<Division_Name,Department_Name> Ans_Percent),

                      if(Source_Key=2,sum ({$<Source_Key={2}>}Ans_Percent)/sum ({$<Source_Key={2}>} TOTAL<Division_Name,Department_Name> Ans_Percent),0))

                       

                      Thanks for your direction - much appreciated.

                       

                      The variables were to make it easier to work with and they are used in other charts, but it looks like they were getting in the way of things.

                       

                      Thanks

                      Rob