article How to use - Dimensionality() in Member Articles

How to use - Dimensionality()

robert_mika — Thu, 19 Feb 2015 11:16:57 GMT

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

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 <140	LEVEL 2 --> Values <=20	LEVEL 3 --> Values <=20

Remember

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)

Re: How to use - Dimensionality()

saurabh5 — Fri, 27 Feb 2015 16:06:10 GMT

Nice way of explaning dimenssionality, helpful topic !

Re: How to use - Dimensionality()

NickHoff — Fri, 27 Feb 2015 22:11:39 GMT

Is it possible to hide a total and still show the dimension, which makes up that total?

Re: How to use - Dimensionality()

senarath — Tue, 03 Mar 2015 05:00:28 GMT

Any other business uses other than cell formatting ?

Thanx

Re: How to use - Dimensionality()

vikasmahajan — Tue, 03 Mar 2015 05:13:57 GMT

Actually from many days we have requirement of such types of presentations in our charts

Thanks for Sharing such nice document.

Vikas

Re: How to use - Dimensionality()

Anonymous — Wed, 11 Mar 2015 09:51:38 GMT

I think this is mainly for the busines user

Re: How to use - Dimensionality()

richard_chilvers — Thu, 12 Mar 2015 16:53:48 GMT

A clear explanantion of a feature which is kind of hidden away but can be vaulable especially when formatting cells as highlighted here.

Re: How to use - Dimensionality()

rustyfishbones — Fri, 13 Mar 2015 09:33:13 GMT

This is the best explanation I've seen on this, thanks.

Re: How to use - Dimensionality()

Tue, 17 Mar 2015 06:59:46 GMT

Very Useful for Pivot..great

Re: How to use - Dimensionality()

Sun, 22 Mar 2015 07:31:26 GMT

Nice post! I've also found Dimensionality() useful when there's a requirement to calculate the total row in a different way than the other rows.

Re: How to use - Dimensionality()

Fri, 19 Jun 2015 10:15:41 GMT

VERY HELPFUL BUT still in doubt, how come it got 1 dimentionality for sugar

Re: How to use - Dimensionality()

robert_mika — Fri, 19 Jun 2015 10:19:27 GMT

Are you referring to this table?

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

In Qlik Dimension=Column

Re: How to use - Dimensionality()

Fri, 19 Jun 2015 10:40:53 GMT

yes this table

Re: How to use - Dimensionality()

robert_mika — Fri, 19 Jun 2015 10:42:50 GMT

So have I answered your question as you wanted?

Re: How to use - Dimensionality()

Digvijay_Singh — Sun, 21 Jun 2015 11:48:10 GMT

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

Thanks,

Re: How to use - Dimensionality()

robert_mika — Sun, 21 Jun 2015 12:10:16 GMT

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)

Re: How to use - Dimensionality()

ahbzshk07 — Mon, 18 Jan 2016 08:04:19 GMT

Very nice... (y)

Re: How to use - Dimensionality()

Chanty4u — Fri, 12 Feb 2016 05:32:44 GMT

awesome..

Re: How to use - Dimensionality()

HirisH_V7 — Tue, 23 Feb 2016 12:16:23 GMT

Very Helpful...

Thanks,

Hirish

Re: How to use - Dimensionality()

kkkumar82 — Thu, 21 Apr 2016 07:09:11 GMT

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