Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Adding a new dimension to pivot table

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

complexitycategorytimetaken
simpleA8
complexB30
averageC17
simple D9
complexE25

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

10 Replies
Specialist
Specialist

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.

Not applicable

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

Master III
Master III

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

Capture.JPG

Not applicable

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.

Not applicable

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?

What all are your dimension and expressions here?

Master III
Master III

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 ];

Not applicable

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

Screen Shot 2016-06-14 at 10.09.50 PM.png

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.

Master III
Master III

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.