Qlik Community

Ask a Question

QlikView Documents

QlikView documentation and resources.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER

How to use - Dimensionality()

robert_mika
Master III
Master III

How to use - Dimensionality()

Definition:


Returns the number of dimension columns that have non-aggregation content. i.e. do not contain partial sums or collapsed aggregates.

A typical use is in attribute expressions, when you want to apply different cell formatting depending on aggregation level of data.

This function is only available in charts. For all chart types except pivot table it will return the number of dimensions in all rows except the total, which will be 0.

What does it mean?

We have Table with 4 dimensions(columns): Product,Category,Type,Sales

table.jpg

Now we want to create Pivot Table by using those Dimensions.

We are going to use only 3 of them(Product,Category,Type) and use 4th(Sales) in our expression.

The result is shown below:

This Pivot Table  has 3 dimensions so its maximum dimensionality is 3.

For better understating please see  table below.

The function is used to show on which dimensionality level each of the Pivot Table row is:

'Sugar' has dimensionality of 1 which is Total for that 'Product'.

'Salt' has dimensionality of 2 which is Total for each 'Category' of that 'Product'.

'Oil' has dimensionality of 3 which is single value for each 'Type' of the 'Product's' 'Category'.

So then more Dimension we use the greater dimensionality of our Pivot Table is.


Practical use:

1) To show the level of dimensionality:

Expression:

if(Dimensionality()=1 ,RGB(151,255,255),if(Dimensionality()=2 ,RGB(0,238,0),if(Dimensionality()=3,RGB(255,130,171))))

2) Highlight background of rows which on each level  fall into certain condition:

Expression:

if(Dimensionality()=1 and sum(Sales)<150,RGB(151,255,255),if(Dimensionality()=2 and sum(Sales)<=20,RGB(0,238,0),if(Dimensionality()=3 and Sum(Sales)<=20,RGB(255,130,171))))

LEVEL1 --> Values <140LEVEL 2 --> Values <=20LEVEL 3 --> Values <=20

Remember ligtbulb.jpg

  • To use the script below the Excel file must be saved in the same folder that your qvd file

        Otherwise you will need to make changes the this path - [Dimensionality.xlsx]

Directory;

LOAD Product,

     Category,

     Type,

     Sales

FROM

[Dimensionality.xlsx]

(ooxml, embedded labels, table is Sheet1);

Felling Qlingry?

How To /Missing Manual (25 articles)

Attachments
Comments
robert_mika
Master III
Master III

Are you referring to this table?

Sugar is the only column (dimension)in this section.

In Qlik Dimension=Column

0 Likes
Not applicable

yes this table

0 Likes
robert_mika
Master III
Master III

So have I answered your question as you wanted?

0 Likes
Digvijay_Singh

Very helpful post! Can you also explain the difference between Dimensionality and the SecondaryDimensionality and specific usage of the same! Thanks in advance!

Thanks,

DJ

0 Likes
robert_mika
Master III
Master III

Secondary Dimensionality is being used for Horizontal dimensions

See here:

The second dimension... or how to use secondarydimensionality()

Still feeling Qlikngry?


How To /Missing Manual(15+ articles)

ahbzshk07
Contributor III
Contributor III

Very nice... (y)

0 Likes
Chanty4u
MVP
MVP

awesome..

0 Likes
HirisH_V7
Master
Master

Very Helpful...

Thanks,

Hirish

0 Likes
kkkumar82
Specialist III
Specialist III

Hi Robert,

Is there a way the sub totals for the different dimensions get there name before the "total" label.

Like Product Total

       Category Total

       Type Total

0 Likes
MindaugasBacius
Partner
Partner

Practical post that I would recommend reading for everybody.

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2015-02-19 06:16 AM
Updated by: