# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Action-Packed Learning Awaits! QlikWorld 2023. April 17 - 20 in Las Vegas: REGISTER NOW
cancel
Showing results for
Did you mean: Not applicable

## 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 Hours 30,532 36,654 40,846 Hours Lost to Sickness 4256 3445 3798 Hours Lost to Annual Leave 2548 2889 2669 Hours Lost to Vacancies 1400 1500 1564

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

9 Replies  Specialist

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  Partner - Specialist III

Hello,

I've worked on this for little bit time.Please check the attached file. May be it helps you.

Cheers!!

Jagan Not applicable
Author

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  Specialist

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 Not applicable
Author

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 Not applicable
Author

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?

Fiorano Not applicable
Author

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  Specialist

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 Not applicable
Author

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 Community Browser