Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
nikhilgarg
Specialist II
Specialist II

What is NoOfColumn() and Column No() ?

Hello,

Can anyone help me the use of Column No() and No of columns() with example ?

thanks

21 Replies
MK_QSL
MVP
MVP

From Help FIle

NoOfColumns([total])

Returns the number of columns in the current row segment in a pivot table.

If the pivot table is one-dimensional or if the qualifier total is used as argument, the current row segment is always equal to the entire row.

If the pivot table has multiple horizontal dimensions, the current row segment will include only columns with the same values as the current column in all dimension rows except for the row showing the last dimension in the inter field sort order. The inter field sort order for horizontal dimensions in pivot tables is defined simply by the order of the dimensions from top to bottom.

Example:

if( ColumnNo( )=NoOfColumns( ), 0, after( sum( Sales )))

ColumnNo([total])

Returns the number of the current column within the current row segment in a pivot table. The first column is number 1.

If the pivot table is one-dimensional or if the qualifier total is used as argument, the current row segment is always equal to the entire row.

If the pivot table has multiple horizontal dimensions, the current row segment will include only columns with the same values as the current column in all dimension rows except for the row showing the last horizontal dimension of the inter field sort order. The inter field sort order for horizontal dimensions in pivot tables is defined simply by the order of the dimensions from top to bottom.

Example:

if( ColumnNo( )=1, 0, sum( Sales ) / before( sum( Sales )))

sunny_talwar

I know ColumnNo() is similar to RowNo(), but for a pivot table when you have one or more dimension going across, not sure if I used NoOfColumn() function before.

nikhilgarg
Specialist II
Specialist II
Author

Hey,

I have already gone thru help file but cann't understand much

MayilVahanan

Hi

In the below example, u can see, No of columns in the pivot table is 2 (Quarter).

And ColumnNo() is 1 for 1Q15 and 2 for 2Q15.

Quarter 1Q152Q15
ID_company Due=NoOfColumns()=ColumnNo()Due=NoOfColumns()=ColumnNo()
150000215000022
21000002110000022
3500002110000022
Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
sunny_talwar

Here is an example for you to understand:

Capture.PNG

Table 1 and Table 2 have both the same number of dimensions and expressions, but since in Table 1 there is one dimension (Dim2) is going across, we are able to use ColumnNo() and NoOfColumn() functions, whereas in otherone we don't see any results.

I hope this will clarify things,

Best,

Sunny

nikhilgarg
Specialist II
Specialist II
Author

Hey,

Thanks. This is exactly what happening in my case. My case is same as of you below table. So how can i use ColumnNo() and NoOfColumns() here?

sunny_talwar

If you don't have a dimension going across, you might have to use RowNo() and NoOfRows() instead of ColumnNo() and NoOfColumns(), because the later 2 are used only when a dimension is pivoted (or goes across). Does that make sense?

sunny_talwar

See if this is what you want?

Capture.PNG

nikhilgarg
Specialist II
Specialist II
Author

Hey,

I tried with ROWNO() and NoOfRows() but it also doesnot work