Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How do i produce a pivot table which lists top 5 answered questions per month?

I'm looking to create a pivot table which is based on a ranking per month of the top five answered questions (based on NO answers) in an office survey, similar to the below.

Rank

(Expressions)

May 16June 16July 16
QUESTIONS NO ANSWERS POS 1NOT HAPPY WITH SERVICEITS TOO COLDTOO MANY LIGHTS OUT

QUESTIONS NO ANSWERS POS 2

TOO MANY LIGHTS OUTNOT HAPPY WITH SERVICEITS TOO COLD
QUESTIONS NO ANSWERS POS 3ITS TOO COLDTOO MANY LIGHTS OUTNOT HAPPY WITH SERVICE
QUESTIONS NO ANSWERS POS 4THE STATIONERY IS CHEEPWE RAN OUT OF COFFEETHE STATIONERY IS CHEEP
QUESTIONS NO ANSWERS POS 5WE RAN OUT OF COFFEETHE STATIONERY IS CHEEPWE RAN OUT OF COFFEE

I assume this has something to do with the aggregation and the like my best efforts so far have resulted in the below straight table

Dimensions:

MonthName

Question (Hidden)

Expression:

IF(Rank(Aggr(Count({<Answer={'No'},User={'*'}-{'Nathalie','Natalie','Emanuele','Anthony'}>}QUESTION),QUESTION,MonthName))<=5,Rank(Aggr(Count({<Answer={'No'},User={'*'}-{'Nathalie','Natalie','Emanuele','Anthony'}>}QUESTION),QUESTION,MonthName)))

Best effort one.JPG

And this table

Best effort 2.JPG

Dimension

MonthName

Expressions

Only(if(aggr(Rank(Count({<Answer={'No'},User={'*'}-{'Nathalie','Natalie','Emanuele','Anthony'}>}QUESTION))=1,QUESTION ),QUESTION))
Only(if(aggr(Rank(Count({<Answer={'No'},User={'*'}-{'Nathalie','Natalie','Emanuele','Anthony'}>}QUESTION))=2,QUESTION ),QUESTION))


Only(if(aggr(Rank(Count({<Answer={'No'},User={'*'}-{'Nathalie','Natalie','Emanuele','Anthony'}>}QUESTION))=3,QUESTION ),QUESTION))

etc....

The second table is closest to what I require however I am currently stuck for ideas as to how to make the expression pull out the questions against each month based on their rank.

Help please!!!!

5 Replies
oknotsen
Master III
Master III

Is this a QlikView or a Qlik Sense question (as you posted it in the wrong area)?

Let us know and we can move it to the right area so it get the right attention.

May you live in interesting times!
Not applicable
Author

Hi it's a Qlikview question

pho3nix90
Creator II
Creator II

HI David,

If I understand correctly you want your output to only display top 5 items by month like your first screenshot at the very top?

Could you provide a qvw so that I can try and assist you?

Not applicable
Author

Hi,

Please see the attached.

David Chalkley

Manager

Deloitte LLP

Direct phone: +44 20 7007 1571 | Mobile: +44 7717 882246

dchalkley@deloitte.co.uk<mailto:dchalkley@deloitte.co.uk> | www.deloitte.co.uk<http://www.deloitte.co.uk/>

pho3nix90
Creator II
Creator II

Can I ask you upload the excel file as well? So that I can do reloads, it seems this will have to be done in the backend