topic Re: please help..pivot table error in QlikView

please help..pivot table error

Thu, 23 Jul 2015 14:48:41 GMT

Hi,

Please refer to attached app.

Common issue were trying to overocome is changing the axis on a pivot table.

On the attached you will see some data. We want this to appear on the pivot table atatched labelled ' format we want to work' where the rating is the x axis and the area is the y. Whilst visually it looks like its working there is errots with the data as it is showing the same values for each option. I,' assuming this is because of the expression which is as follows

([Rating]= 'Delivered')

Qlikview says ;'expression ok' (and it makes sense) but we know this is incorrect because when you revert to the standard way Qlikviewo presents data (see table entitled 'how currenrtly works') the numbers are correct.

It's mad but what were trying to do is really easy in pivot tables functionality in excel but we would prefer not doing the all the good work developing an app to then have to export to excel to do any analsyis.

Any help would be greatly apprecieted.

Chris

Re: please help..pivot table error

sunny_talwar — Thu, 23 Jul 2015 14:56:38 GMT

Try these expressions:

Count({<Rating = {'Delivered'}>} Rating)

Count({<Rating = {'Excelled'}>} Rating)

Count({<Rating = {'Inspirational'}>} Rating)

Re: please help..pivot table error

awhitfield — Thu, 23 Jul 2015 15:00:51 GMT

Hi Chris,

is this what you want?

Count({<Rating = {'Delivered', 'Excelled','Inspirational' }>}[Rating])

Andy

Re: please help..pivot table error

Thu, 23 Jul 2015 15:08:11 GMT

Cheers..that worked on test app; however, we have another field called 'Consider Move' but when trying to adapt expression it doesnt like it..? - guess its because its 2 words...

Count([<:Consider Move = {'Delivered'}>] Consider Move)

Chris

Re: please help..pivot table error

awhitfield — Thu, 23 Jul 2015 15:11:05 GMT

Hi Chris,

you need to apply square brackets around it

Count([<[Consider Move] = {'Delivered'}>] [Consider Move])

Andy

Re: please help..pivot table error

Thu, 23 Jul 2015 15:21:51 GMT

Hi Andrew,

Tried it:

Count([<[PDR Outcome:] = {'Delivered'}>][PDR Outcome:])

Doesn't like it...

Re: please help..pivot table error

vishsaggi — Thu, 23 Jul 2015 15:39:56 GMT

Hi Chris,

Hope you are using the Flower bracket when using set.

Try this:

Count({<[PDR Outcome:] = {'Delivered'}>} [PDR Outcome:])

Thanks,

Re: please help..pivot table error

awhitfield — Thu, 23 Jul 2015 15:40:46 GMT

Are there really colons in the field name? Also you have an additional ] after {'Delivered'}>]

Andy

Re: please help..pivot table error

Thu, 23 Jul 2015 15:47:51 GMT

removed colons.

Thanks for your help

Re: please help..pivot table error

Thu, 23 Jul 2015 16:01:57 GMT

Can anyone confirm how to have a column that shows this as % too:

Here is correct expression for number.

({<[PDR Outcome] = {'Delivered'}>} [PDR Outcome])

Re: please help..pivot table error

vishsaggi — Thu, 23 Jul 2015 16:18:55 GMT

Hi Chris,

Go to Chart Properties and Number Tab and Check "Show in Percent (%)" for that expression.

Try that.

Re: please help..pivot table error

Thu, 23 Jul 2015 16:27:19 GMT

Hi,

That isn't available - greyed out. I have worked it out. FYI:

({<[PDR Outcome] = {'Delivered'}>} [PDR Outcome])/ Count(TOTAL [PDR Outcome])

Do you know hot to show totals at both the end of rows and columns?

Chris

Re: please help..pivot table error

vishsaggi — Thu, 23 Jul 2015 16:28:03 GMT

Hi Chris,

PFA the qvw file is this what you are looking for let us know..

Re: please help..pivot table error

sunny_talwar — Thu, 23 Jul 2015 16:31:20 GMT

Like this?

Re: please help..pivot table error

vishsaggi — Thu, 23 Jul 2015 16:31:56 GMT

Hi Chris,

Great you got that. If you want subtotals in the bottom. Go to presentation tab, select the Dimenstion Area and check

"Show partial sums". I am not pretty sure to get the granttotal beside the last column might need to look into it sorry.