topic Re: Adding a new dimension to pivot table in QlikView

Adding a new dimension to pivot table

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

Currently, I have the following data presented in listbox in qlikview as follows:

Where complexity is being defined as an expression such that: simple: timetaken<11, average: 11 <timetaken<20, complex >20

complexity	category	timetaken
simple	A	8
complex	B	30
average	C	17
simple	D	9
complex	E	25

May I know if there is anyway to put this into a pivot table such that the first dimension will be complexity, followed by category, and the expression will be timetaken?

I tried taking the same expression for complexity and copy into the calculated dimension in pivot table. but it shows calculation error.

P.S: Please do not attach qlikview files, I cant seem to download and view it on my qlikview.

Thanks

Re: Adding a new dimension to pivot table

juleshartley — Tue, 14 Jun 2016 08:47:46 GMT

The cleanest way to do this is to build the 'complexity' field in the script. If there is no aggregation function in the expression then you could use a calculated dimension.

You cannot use aggregation fields in the dimension field of a chart so if that is what you need to do then you have a more complex issue.

Re: Adding a new dimension to pivot table

Tue, 14 Jun 2016 09:00:39 GMT

could you show me how to build the complexity field in the script?

Currently my code are:

Load

Category,

timetaken

FROM<>

where and how should I create a new field?

Would really appreciate if you would be able to type out the coding to it?

Thanks

Re: Adding a new dimension to pivot table

Digvijay_Singh — Tue, 14 Jun 2016 09:04:24 GMT

If I assume category and timetaken as fields, you may do like this -

Re: Adding a new dimension to pivot table

Tue, 14 Jun 2016 09:08:38 GMT

may I know what table is this? and where do you key in the codes(the dimension or expression field?

but if I were to do in this table, I would not be able to get the categories which are classified under simple, and the corresponding timetaken.

Re: Adding a new dimension to pivot table

Tue, 14 Jun 2016 09:11:50 GMT

oh. right. im sorry. I didn't see it correctly. yes. your table is the pivot table im looking at. But I have no idea, where should I type the code in.

should I type it in add in dimension>> add calculated dimension?

Re: Adding a new dimension to pivot table

sunny_talwar — Tue, 14 Jun 2016 09:42:55 GMT

What all are your dimension and expressions here?

Re: Adding a new dimension to pivot table

Digvijay_Singh — Tue, 14 Jun 2016 13:36:35 GMT

Oh, I think I missed your messages, see the attached sample which I used here -

sample script is -

Load * inline [

Category, timetaken

A, 8

B, 30

C, 17

D, 9

E, 25 ];

Re: Adding a new dimension to pivot table

Tue, 14 Jun 2016 14:11:49 GMT

there is no sample attached. And please do not attach any sample. i can't seem to download and open qv file.

yea. i would like to know how to know. how to obtain this part of the pivot table, if one only has the category and time taken on hand.

Re: Adding a new dimension to pivot table

Digvijay_Singh — Tue, 14 Jun 2016 16:50:15 GMT

This expression added as dimension is called calculated dimension, here are steps to create it -

Step 1 - Right click and select properties option of Pivot table chart

Step 2- Click on Dimension tab

Step 3 - Click on 'Add Calculated Dimension' button in the Upper right section

Step 4 - Add following expression here -

=If(timetaken<11,'Simple',

if(timetaken>11 and timetaken<20,'Average','Complex'))

Step 5 - Add second dimension Category ( I assume you have reloaded the script by now and able to see Category in dimension list)

Step 6 - Go to expression tab and add the expression - Only(timetaken)

I think you are done to see the results I have pasted in last post of mine.

Re: Adding a new dimension to pivot table

Wed, 15 Jun 2016 01:20:20 GMT

It says calculation error when I were to do step 4.

my original expression is

if((interval(if(turnaroundtime>=0,Round(Avg(turnaroundtime)))/86400,'d:hh:mm:ss'))<1,'Simple',
if((interval(if(turnaroundtime>=0,Round(Avg(turnaroundtime)))/86400,'d:hh:mm:ss'))>=1 and (interval(if(turnaroundtime>=0,Round(Avg(turnaroundtime)))/86400,'d:hh:mm:ss'))<3,'Moderate',
if((interval(if(turnaroundtime>=0,Round(Avg(turnaroundtime)))/86400,'d:hh:mm:ss'))>=3 and (interval(if(turnaroundtime>=0,Round(Avg(turnaroundtime)))/86400,'d:hh:mm:ss'))<10,'Advanced','Complex')))

It surfaced calculation error.