Calculated Dimensions within Pivot - Qlik Community

Report Inappropriate Content · ‎2012-12-14

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 Hours	30,532	36,654	40,846
% Of Total Available Hours Lost to Sickness	13.94%	9.40%	9.30%
Hours Lost to Sickess	4256	3445	3798
% Of Total Available Hours Lost to Annual Leave	8.35%	7.89%	6.54%
Hours Lost to Annual Leave	2548	2889	2669
% of Total Available Hours Lost to Vacancies	4.59%	4.10%	3.38%
Hours Lost to Vacancies	1400	1500	1564

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

Anonymous · ‎2012-12-14

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

jagannalla · ‎2012-12-14

Hello,

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

Cheers!!

Jagan

Report Inappropriate Content · ‎2012-12-14

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

Anonymous · ‎2012-12-14

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

Report Inappropriate Content · ‎2012-12-18

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

Report Inappropriate Content · ‎2013-01-02

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

Report Inappropriate Content · ‎2013-01-03

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

Anonymous · ‎2013-01-04

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

Report Inappropriate Content · ‎2013-01-04

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