topic Re: Pivot Table Values As Percentage in QlikView

Pivot Table Values As Percentage

Fri, 27 Jun 2014 14:14:23 GMT

Hi all,

Please refer to the attached screenshot.

I am trying to work out how I can show the values this pivot table as a percentage of the total row number.
E.g. in the first row, I would like to see value 29/1520, to give 1.9%

That value 29 is an expression setup in the pivot table.

Any help would be much appreciated.

Thanks

Re: Pivot Table Values As Percentage

jerem1234 — Fri, 27 Jun 2014 14:17:39 GMT

Depending on your dimensions, you can try something along the lines of:

sum(Sales)/sum(total Sales)

Or if you have a dimension, you can try something like:

sum(Sales)/sum(total <DIM1> Sales)

sum(Sales)/sum(total <DIM1, DIM2> Sales)

etc.

Hope this helps!

Re: Pivot Table Values As Percentage

Gysbert_Wassenaar — Fri, 27 Jun 2014 14:19:12 GMT

If your expression is now sum(X) change it to if(dimensionality()=0, sum(X)/sum(total <DimA> X), sum(X)), but replace DimA with the name over which you want the percentage calculated.

Re: Pivot Table Values As Percentage

Fri, 27 Jun 2014 14:28:31 GMT

Here is my expression which calculates the row values. The TOTAL at the end of the row comes from ticking, show partial totals for the expression.

Can anyone tell me how to edit this:

Thanks

Count(if( AssessmentDate >= $(vAssessmentStartDate) AND AssessmentDate <= $(vAssessmentEndDate), MarkRange)))

Re: Pivot Table Values As Percentage

jerem1234 — Fri, 27 Jun 2014 14:46:44 GMT

Something like:

Count(if( AssessmentDate >= $(vAssessmentStartDate) AND AssessmentDate <= $(vAssessmentEndDate), MarkRange))/Count(total <DIM1> if( AssessmentDate >= $(vAssessmentStartDate) AND AssessmentDate <= $(vAssessmentEndDate), MarkRange))

Replace DIM1 with whatever dimension you are using.

As Gysbert Wassenaar shows, you'll also want to add the condition of dimensionality() so you have the correct sum for your total as well as your other values.

Hope this helps!

Re: Pivot Table Values As Percentage

Fri, 27 Jun 2014 14:49:38 GMT

Thank Jerem124, however that gives a percentage value of the column total, not the row. I.e. it is taking 29/column total at the bottom, where I am looking for the percentage of the row total.

Thanks

Re: Pivot Table Values As Percentage

jerem1234 — Fri, 27 Jun 2014 14:52:43 GMT

Play around with the <DIM1> to try and get the correct value, if you have more than one dimension you may need to add like:

<DIM1, DIM2>

I don't know what dimensions you are using or how many.

Re: Pivot Table Values As Percentage

Fri, 27 Jun 2014 14:57:03 GMT

This is my dimension: Thanks

Count(if( AssessmentDate >= $(vAssessmentStartDate) AND AssessmentDate <= $(vAssessmentEndDate), MarkRange)))

Re: Pivot Table Values As Percentage

jerem1234 — Fri, 27 Jun 2014 15:03:01 GMT

Oh that's called an Expression, on the Dimension tab will have your list of dimensions. The dimension that's the first column of your chart (maybe its employees or students or person etc.). That's the dimension you want.

If you want either upload a pic of the entire chart or create a sample document that demonstrates the same issue.

Re: Pivot Table Values As Percentage

Mon, 30 Jun 2014 06:49:29 GMT

Thanks.
So I now have the following with my dimension being the <Markrange>.

It works, but it is giving the % value as a % of the Column total, rather than the Row total (if that makes sense).
Thanks

(if( AssessmentDate >= $(vAssessmentStartDate) AND AssessmentDate <= $(vAssessmentEndDate), Centre))/Count(total <MarkRange> if( AssessmentDate >= $(vAssessmentStartDate) AND AssessmentDate <= $(vAssessmentEndDate), MarkRange))

Re: Pivot Table Values As Percentage

Mon, 30 Jun 2014 07:21:35 GMT

Thanks Jeremiah, managed to work this out in the end.
So the solution was to use your dimensions in the <DIM1> values.

Much appreciated!