Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Does anyone know how to create multiple rows in pivot table situations?

Hi,


I am relatively new to Qlikview and I have been set a challenge to generate an application around a recent survey conducted by my workplace.

My initial thoughts were to generate a pivot table that would have columns of data such as gender, ages, overall totals and percentages and then within the rows themselves I would build in the variety of questions and their options as detailed in the survey i.e. 'Q1: What did you think of this service?' - 'Option 1: Very good - Option 2: Good', etc..

Nothing is as straight forward as I had first thought as each time I add an additional question beneath existing row it basically adds it in a drill down format i.e. 'Q1: What did you think of this service?' followed by 'Question 2' which is added as a sub category of question 1 where as I want it to be a seperate and new entry within the row fields.

The attached excel spreadsheets hopefully demonstrates clearly what I currently am viewing (tab 1) and what I want to be able to achieve with this (tab 2).

At present, I have adopted a 'Cyclic Group' process to the application where I am able to rotate the questions round in order to obtain the results I need.  However this isn't really the ideal outcome I was hoping for since I want to be able to show all of the data in a pivot table format which can than be transported across to Excel for further examination or for distribution.

Any help or advice would be gratefully received.

Thanks and regards, Mike.

19 Replies
mazacini
Creator III
Creator III

Hi Mike

There are a number of issues here. I'm not sure of I can get my head around each, but let me clarify the nature of the data firstly.

Firstky, can you confirm how you capture the gender and age data for each respondent?

Simplest would be if there were 2 additional columns in the RawData tab, one for gender, and one for Age group

You then have 2 dimensions that you can use to analyse.

Joe

Not applicable
Author

Hi Joe,

I  can confirm that 'Gender' and 'Age' (as do all of the data) have columns within the original dataset where entries are made within the rows as per the attachment on 17th May 2012 which gives you an idea of how the data looks.

Regards, Mike.

mazacini
Creator III
Creator III

OK. You will need to amend the crosstable load to pull in this information.

In the RawDat, I insert 2 new columns (Age and Gender) to the right of the Respondent Column.

My script changes to

CrossTable(Sub, Response,3)

This creates and additional 2 quaifier fields (Age and Gender). Age and Gender are now available as Dimensions in the modle, and can be used to extend the pivot for analysing repsonses/respondents.

Not applicable
Author

Hi Joe,

Hope this finds you well.

I just wanted to say many thanks for your solution to my previous query.  It has resolved a lot of issues from our point of view and has created all kinds of possibilities with this new matrix.

What I am now attempting to achieve with the new layout is calculating percentages, which I thought would be a fairly straightforward thing to achieve is proving somewhat elusive.  Perhaps I have overcomplicated what I am attempting to achieve.

What I want to be able to achieve is columns where the total equate to 100% and the cells within each column to be calculated as a percentage of the overall total for the selected criteria.

The attached spreadsheet shows the ‘Current outcome’ which is what I am experiencing at present where the calculations are determining percentages as an overall total of each row.

I think the problem lies in that I have two tiers working within the columns which are making the calculations less straightforward as I would like it to be.

I know there is a simple way of overcoming this but at this stage all my attempts thus far to get it to work is proving fruitless.

The calculation I have adopted (detailed in the spreadsheet) will work if only one of the two tiers appear in the columns but not when there are more than one tier from within the columns.

Any help would be gratefully appreciated.

Thanks and regards, Mike.

mazacini
Creator III
Creator III

Hi Mike

Haven't abandoned you.

Just haven't been on the site for a few days.

Try this:

=count(Response)/count(TOTAL <Gender>Response)

Rgds

Joe

Not applicable
Author


Hi Joe,

I have just got around to trying this out and I am not getting the desired result.

I copied your suggested expression as per:

=count(Response)/count(TOTAL <Q1 Gender> Response)

I notice that the <Q1 Gender> Response)remained black. I have attempted a variety of different options such as inserting brackets at various points in the expression but to no avail.  The percentage column in the matrix returns dashes where the data should be.  Any suggestions?

Thanks and regards, Mike.

mazacini
Creator III
Creator III

Hi Mike

I may have mislead you,as I named your Tier Two dimension as Gender.

If you replace the word Gender with the name of the Tier Two dimension, does it work?

Joe

Not applicable
Author

Joe,

With a bit of twiddling and fiddling I came up with this solution:

=count(Response)/count(TOTAL) <[Q1 Gender]> Response)

...and it works!!

Thanks and regards, Mike.

mazacini
Creator III
Creator III

Hi Mike

Well done on that.

If any of the above answers proved correct ot hepful, you might mark them accordingly?

Rgds

Joe

Not applicable
Author

Hi Joe,

On presenting the new matrix to my line manager a query was raised as to whether the percentage calculation could be reworked to look something along the lines of the attached spreadsheet.

The green cells is what I want to be able to achieve.  It is basically a percentage of the total sum of the 'Sub' questions for each of the 'Responses' ((Selected or Not Selected / Q7a or Q7b or Q7c and so forth) or (1063 / 1320 for Q7a Not Selected and 257 / 1320 for Q7a Selected and so forth)).

Is there an easy way of achieving this, as this has me somewhat baffled at present in attempting to translate this simple calculation into script format.

Hopefully this will be last thing I need to do to complete this matrix.

Many thanks and regards, Mike.