9 Replies Latest reply: Jan 4, 2013 5:48 AM by fiorano2012 RSS

    Calculated Dimensions within Pivot

      Hi,

       

      I have a pivot with a dimension called 'Question' which is the sum of 'val3'.  What I need to do is create a second Dimension which is the % rate for  certain question dimensions based on the value of Total Available Hours eg :

       

       

      Current Pivot Layout

       

       

      Question
      April
      May
      Jun
      Total Avaiable Hours30,53236,65440,846
      Hours Lost to Sickness425634453798
      Hours Lost to Annual Leave254828892669
      Hours Lost to Vacancies140015001564

       

       

      Required Layout

       

      Question
      April
      May
      Jun
      Total Available Hours30,53236,65440,846
      % Of Total Available Hours Lost to Sickness13.94%9.40%9.30%
      Hours Lost to Sickess425634453798
      % Of Total Available Hours Lost to Annual Leave8.35%7.89%6.54%
      Hours Lost to Annual Leave254828892669
      % of Total Available Hours Lost to Vacancies4.59%4.10%3.38%
      Hours Lost to Vacancies140015001564

       

      I need to 'inject' new % dimensions for certain questions (these questions have an ID number eg, Hours Lost to Sickness is ID 44).  The new Rates need to be displayed in this order too.

       

      Is this requirement possible?

       

      Many thanks for any help!

      Fiorano

        • Re: Calculated Dimensions within Pivot
          Jonathan Brough

          I've done such %age rows in the past, using the following approach:

           

          1. Add the %age questions in to your list of questions as they need to be dimensions in order to show up as rows in your table

           

          2. Within the expresson, test whether the question is a calculation, maybe using Left(Question,1)= '%'. This is so you can decide whether to perform the usual sum, or to do a calcuation of the %age

           

          3. Determine the numerator on the row above. If it is in the order you show you can use the Above(expression), but you will then need to restrict the ability for users to resort the table.

           

          4. Determine the denominator from the top row of the table, using Top(expression).

           

          So, in summary, your expression would look something like the following:

           

          =if(Left(Question,1)= '%',

            Above(sum(Value))/Top(sum(Value)),

            sum(Value)

          )

           

          To make it more efficient you could add another field alongside the Question, something like QuestionType, which you could set to normal or calulated and test on instead of looking for the first character.

          Hope this helps.

          Jonathan

           

          Message was edited by: jonbroughavone Edited to make Step 4 easier and offer just one example expression

          • Re: Calculated Dimensions within Pivot

            Thanks very much for both of your replies!!!  They are both solutions to what I am after.  Thank you Jagan too for your example file - very much appreciated.

             

            I should have posted my expression which is :

             

            =if(dashboard=vIndicator,If(single_value='N',num((sum(val1)/sum(val2)*100),'#,##0%'),

            sum(val3)))

             

            • The Pivot is limited by the variable held in 'vIndicator'
            • The Second Part of the expression checks if final value is a Number or a Rate
            • Also,  there are alot more 'Questions' than those I have posted. The ones I posted are those that require the Added % questions. 

             

            Do any of these points impact on the solutions you both suggested?

             

            Thanks again,

            Fiorano

              • Re: Calculated Dimensions within Pivot
                Jonathan Brough

                The whole expression would need to go into the various Above(), Below() and / or Top() formulas, which might mean a lot of duplication and maintenance so you may want to move the logic to the script or a calulated dimension.

                Sounds like you can use the single_value field for determining whether to do a %age calculation or not.

                If there are a larger number of such %age calulations it would be necessary for them to all be below (sorry, I was stating Above before) in order to assure being able to find the numerator.

                Jonathan

                  • Re: Calculated Dimensions within Pivot

                    Hi Jonathan,

                     

                    I have used Jagan's example and have the following expression  :

                     

                    =Pick(0+Match(question_description,'% of establishment hours lost to annual leave',
                    '% of establishment hours lost to sickness',
                    '% of hours lost short term sickness',
                    '% of hours lost long term sickness',
                    '% of establishment hours lost to Study Leave',
                    '% of hours lost due to vacancies',
                    '% of hours lost due to other'
                    )
                    ,
                    Below(If(single_value='N',num((sum(val1)/sum(val2)*100),'#,##0%'), sum(val3)))/Above(If(single_value='N',num((sum(val1)/sum(val2)*100),'#,##0%'), sum(val3)))*100&'%',
                    Below(If(single_value='N',num((sum(val1)/sum(val2)*100),'#,##0%'), sum(val3)))/Above(If(single_value='N',num((sum(val1)/sum(val2)*100),'#,##0%'), sum(val3)),3)*100&'%',
                    Below(If(single_value='N',num((sum(val1)/sum(val2)*100),'#,##0%'), sum(val3)))/Above(If(single_value='N',num((sum(val1)/sum(val2)*100),'#,##0%'), sum(val3)),5)*100&'%',
                    Below(If(single_value='N',num((sum(val1)/sum(val2)*100),'#,##0%'), sum(val3)))/Above(If(single_value='N',num((sum(val1)/sum(val2)*100),'#,##0%'), sum(val3)),7)*100&'%',
                    Below(If(single_value='N',num((sum(val1)/sum(val2)*100),'#,##0%'), sum(val3)))/Above(If(single_value='N',num((sum(val1)/sum(val2)*100),'#,##0%'), sum(val3)),9)*100&'%',
                    Below(If(single_value='N',num((sum(val1)/sum(val2)*100),'#,##0%'), sum(val3)))/Above(If(single_value='N',num((sum(val1)/sum(val2)*100),'#,##0%'), sum(val3)),11)*100&'%',
                    Below(If(single_value='N',num((sum(val1)/sum(val2)*100),'#,##0%'), sum(val3)))/Above(If(single_value='N',num((sum(val1)/sum(val2)*100),'#,##0%'), sum(val3)),13)*100&'%',
                    if(single_value='N',num((sum(val1)/sum(val2)*100),'#,##0%')),
                    if(single_value='N',num((sum(val1)/sum(val2)*100),'#,##0%')),
                    if(single_value='N',num((sum(val1)/sum(val2)*100),'#,##0%')),
                    if(single_value='N',num((sum(val1)/sum(val2)*100),'#,##0%')),
                    if(single_value='N',num((sum(val1)/sum(val2)*100),'#,##0%')),
                    if(single_value='N',num((sum(val1)/sum(val2)*100),'#,##0%')),
                    if(single_value='N',num((sum(val1)/sum(val2)*100),'#,##0%')),
                    )

                     

                    This is 'injecting' calculated dimensions in the correct place.  However there are no 'values' shown in the  other dimensions eg:

                     

                    Number of available hours                         -

                    % of establisment hours lost to annual leave      15.9955977%

                    Number of hours lost to annual leave              -

                    % of establishment hours lost to sickness         8.32231343%

                    Number of hours lost to sickness                  -

                     

                    Also - there are about 40 other question above the 'Number of Available hours' which are a mixture of %ages and values.  Will the pick/match expression take these into account too?

                     

                    Any help appreciated!!!!

                     

                    Many thanks again,

                     

                    Fiorano

                    • Re: Calculated Dimensions within Pivot

                      Hi Again,

                      Sorry to bother you.  I am still working on this problem and have the following.  Ive had to put a 'match' in in the expression for each 'question' that is available.  My current expression looks like :

                       

                      =Pick(0+Match(Question,
                      
                      '% Question 1',
                      '% Question 2',
                      '% Question 3',
                      '% Question 4',
                      '% Question 5',
                      '% Question 6',
                      '% Question 7',
                      '% Question 8',
                      '% Question 9',
                      '% Question 10',
                      'Total (Question 11)',  //denominator for all calculated dimensions
                                             
                                             '% 1st Calculated Dimension',
                                             '% 2nd Calculated Dimension', 
                                             '% 3rd Calculated Dimension',
                                             '% 4th Calculated Dimension',
                                             '% 5th Calculated Dimension',
                                             '% 6th Calculated Dimension',
                                             '% 7th Calculated Dimension',
                                             '% 8th Calculated Dimension',
                                             
                                             'Number of hours lost …..1 (Question 12)',
                                             'Number of hours lost …..1 (Question 13)', 
                                             'Number of hours lost …..1 (Question 14)',
                                             'Number of hours lost …..1 (Question 15)',
                                             'Number of hours lost …..1 (Question 16)',
                                             'Number of hours lost …..1 (Question 17)',
                                             'Number of hours lost …..1 (Question 18)',
                                             'Sum Question 19',
                                             'Sum Question 20',
                                             'Sum Question 21'
                      
                                             
                                             )
                      ,num((sum(val1)/sum(val2)),'#,###.00%')
                      ,num((sum(val1)/sum(val2)),'#,###.00%')
                      ,num((sum(val1)/sum(val2)),'#,###.00%')
                      ,num((sum(val1)/sum(val2)),'#,###.00%')
                      ,num((sum(val1)/sum(val2)),'#,###.00%')
                      ,num((sum(val1)/sum(val2)),'#,###.00%')
                      ,num((sum(val1)/sum(val2)),'#,###.00%')
                      ,num((sum(val1)/sum(val2)),'#,###.00%')
                      ,num((sum(val1)/sum(val2)),'#,###.00%')
                      ,num((sum(val1)/sum(val2)),'#,###.00%')
                      ,Sum(val3)
                      
                      ,Below(If(single_value='N',num((sum(val1)/sum(val2)),'#,###.00%'), num(sum(val3),'#,###.00')))/Above(If(single_value='N',num((sum(val1)/sum(val2)),'#,###.00%'), num(sum(val3),'#,###.00')))*100&'%'
                      ,Below(Value)/Above(Value,3)*100&'%'
                      ,Below(Value)/Above(Value,5)*100&'%'
                      ,Below(Value)/Above(Value,7)*100&'%'
                      ,Below(Value)/Above(Value,9)*100&'%'
                      ,Below(Value)/Above(Value,11)*100&'%'
                      ,Below(Value)/Above(Value,13)*100&'%'
                      ,Below(Value)/Above(Value,15)*100&'%'
                      
                      ,Sum(val3)
                      ,Sum(val3)
                      ,Sum(val3)
                      ,Sum(val3)
                      ,Sum(val3)
                      ,Sum(val3)
                      ,Sum(val3)
                      ,Sum(val3)
                      ,Sum(val3)
                      ,Sum(val3)
                      ,Sum(val3)
                      
                      )
                      

                       

                      I've attached an image of what the results look like.  Everything is there apart from the calculated dimesions. Ive tried my original expression - but to no avail...

                       

                      I cant work out what do with the Below()/Above() calculations.... As Im referencing each question, I guess I dont need to test if it is a % or a Value calculation?

                       

                      Would really appreciate any help.....

                       

                      Regards,

                      Fiorano

                        • Re: Calculated Dimensions within Pivot
                          Jonathan Brough

                          Hi Fiorano,

                          It may be that this Pick() and Match() solution becomes complex when as many as 40 questions are being processed. I cannot however advise on the implementation of this solution.

                          I personally would prefer to pick out a pattern in the structure of the chart and use expressions that work in multiple instances. I would therefore refer you to the solution I suggested on 14-Dec-2012 08:29, further up this thread.

                          Up to you whether you start again, but attached is something that achieves the intial question in a few lines, which should also work for more questions if they're in the same structure (i.e. above the denominator with the nominator on the top row).

                          Jonathan