What is NoOfColumn() and Column No() ?

[nikhilgarg]

Hello,

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

thanks

[MK_QSL]

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]

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	1Q15			2Q15
ID_company	Due	=NoOfColumns()	=ColumnNo()	Due	=NoOfColumns()	=ColumnNo()
1	50000	2	1	50000	2	2
2	100000	2	1	100000	2	2
3	50000	2	1	100000	2	2

[sunny_talwar]

Here is an example for you to understand:

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]

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?

[nikhilgarg]

Hey,

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