topic Re: Pivot table help in QlikView

Pivot table help

Wed, 18 Nov 2015 20:46:02 GMT

I have a pivot table pulling data from my dataset, which contains a number of questions, several possible answers to each question (satisfied, need to learn....etc), several report periods, and a unique userID for each person that answered the questions.

I have a pivot table that works, but only if I select at least 1 question from the list of questions. Here is my current expression (given to me as a starting point):

count(RespondentID) / aggr(nodistinct count(distinct RespondentID), Question, ReportPeriod)

and I have it formatted as a number (##.##%).

My dimensions are: Answer and ReportPeriod

What I'm seeing, is that if I don't have any of the questions selected, my percentage for Satisfied is 1274.89%, another is 145.44%....etc.

As soon as I select a question though, the percentages correct themselves.

Thanks in advance for any and all help!

Tim

Re: Pivot table help

jolivares — Wed, 18 Nov 2015 21:38:39 GMT

I already make one report like for human resources. It's possible that you need to aggregate your data in order your percentages show the correct numbers.

Try to post some sample date to if I can help you.

Re: Pivot table help

Anonymous — Wed, 18 Nov 2015 23:11:09 GMT

Hi Timothy,

Without looking into the data here my first guess is that the denominator in your expression is getting calculated with a much lesser value. It would be worth to check if this is due to distinct (as there is no distinct in numerator )? Secondly try using total <> qualifier . Eg:
count(total <Question> RespondentID) / aggr(nodistinct count(RespondentID), Question, ReportPeriod)

Regards

Nakul

Re: Pivot table help

ramasaisaksoft — Thu, 19 Nov 2015 06:50:14 GMT

Hi Timothy,

we were not understanding ur question properly so,

it is possible to give solution when u share some sample data.

and let us know what is result u want exactly?

Re: Pivot table help

nagarjuna_kotha — Thu, 19 Nov 2015 07:03:59 GMT

Share some sample data to get quick solution.

Regards,

Nagarjna

Re: Pivot table help

Thu, 19 Nov 2015 13:05:07 GMT

Sorry everybody, just trying to get sample data uploaded. I'm hoping that a .csv file will be okay??? I'm not sure how I attach a file though....

Re: Pivot table help

jonathandienst — Thu, 19 Nov 2015 13:55:50 GMT

It may be as simple as adding the other pivot dimension to your aggr dimensions:

=count(RespondentID) / aggr(nodistinct count(distinct RespondentID), Question, ReportPeriod, Answer)

As a rule, you need all the chart dimensions (as a minimum) in your Aggr().

Re: Pivot table help

Thu, 19 Nov 2015 14:39:23 GMT

Answer	Question	Region	ReportPeriod	RespondentID	SBU
Satisfied	Internet	Asia	10/1/2014	1	SBU1
Need to learn	Internet	US	10/1/2014	2	SBU2
About the same	Internet	US	5/1/2014	2	SBU2
Satisfied	Homepage	Asia	10/1/2014	1	SBU1
Doesnt meet needs	Homepage	Europe	10/1/2014	3	SBU3
Satisfied	Mobile	Asia	10/1/2014	4	SBU4
Satisfied	Mobile	Asia	10/1/2014	5	SBU3
Doesnt meet needs	Storage	Europe	10/1/2014	3	SBU3
Need to learn	Storage	US	10/1/2014	2	SBU2
Satisfied	Storage	Asia	10/1/2014	5	SBU3
Need to learn	Storage	Asia	5/1/2014	1	SBU1
Doesnt meet needs	Storage	Europe	10/1/2013	6	SBU3
Satisfied	Storage	Asia	10/1/2013	7	SBU1
About the same	Storage	US	5/1/2014	8	SBU4
Need to learn	Storage	Europe	5/1/2014	9	SBU1

Sorry, I couldn't find how to attach a CSV, but this is a sample of what I'm dealing with.

Thanks,

Tim