
Re: Calculated Dimensions within Pivot
Jonathan Brough Dec 14, 2012 8:29 AM (in response to fiorano2012)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
Jagan Nalla Dec 14, 2012 8:18 AM (in response to Jonathan Brough)Hello,
I've worked on this for little bit time.Please check the attached file. May be it helps you.
Cheers!!
Jagan

Community_30.qvw 153.0 K

Re: Calculated Dimensions within Pivot
fiorano2012 Jan 2, 2013 5:49 AM (in response to Jagan Nalla)Hi Again,
Im still trying to get this to work. Can you explain the first part of the expression please :
=Pick(0+Match....
How do these funtions work?
Many thanks for your time.
Fiorano



Re: Calculated Dimensions within Pivot
fiorano2012 Dec 14, 2012 9:44 AM (in response to fiorano2012)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 Dec 14, 2012 9:59 AM (in response to fiorano2012)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
fiorano2012 Dec 18, 2012 7:23 AM (in response to Jonathan Brough)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
fiorano2012 Jan 3, 2013 10:56 AM (in response to Jonathan Brough)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 Jan 4, 2013 3:10 AM (in response to fiorano2012)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 14Dec2012 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

Percentage calc in table.qvw 158.8 K

Re: Calculated Dimensions within Pivot
fiorano2012 Jan 4, 2013 5:48 AM (in response to Jonathan Brough)Thanks Jonathan!
I think you are right  the solution is too complex for this number of questions. I will try and to it in the initial script loading..
thanks again for your time (and patience!)
Fiorano


