Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Discussion Board for collaboration related to QlikView App Development.

Announcements

Action-Packed Learning Awaits! QlikWorld 2023. April 17 - 20 in Las Vegas: **REGISTER NOW**

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Calculated Dimensions within Pivot

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Showing results for

Not applicable

2012-12-14
06:43 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

747 Views

9 Replies

jonbrough

Specialist

2012-12-14
07:13 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Partner - Specialist III

2012-12-14
08:18 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hello,

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

Cheers!!

Jagan

Not applicable

2012-12-14
09:44 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

284 Views

jonbrough

Specialist

2012-12-14
09:59 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

284 Views

Not applicable

2012-12-18
07:07 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

284 Views

Not applicable

2013-01-02
05:49 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

284 Views

Not applicable

2013-01-03
10:56 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

284 Views

jonbrough

Specialist

2013-01-04
03:10 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

284 Views

Not applicable

2013-01-04
05:48 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

284 Views