topic how to count rows in dynamic pivot table in QlikView

how to count rows in dynamic pivot table

Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

New in Qlikview I need your help. I have an application and need in a textbox display the number of rows in my pivot table.

The problem is that that pivot table is dynamic (and empty by default) and the user select the dimensions and metrics which have to be shown

is there any way to put in text box some formula like CountRow(total) of idobjectCH10 ?

Thanks for your help

Re: how to count rows in dynamic pivot table

vishsaggi — Thu, 27 Apr 2017 15:34:25 GMT

What are the dimensions used here?

Re: how to count rows in dynamic pivot table

shiveshsingh — Thu, 27 Apr 2017 15:48:02 GMT

Please share sample data, would like to know the dimensions.

Re: how to count rows in dynamic pivot table

rwunderlich — Thu, 27 Apr 2017 23:34:04 GMT

Unfortunately, there is no CountObjectRows(CHxx) function. What you can do is count the combinations of the dimension values of the chart. For example, if your chart used the Dimensions: Customer, Year, Product. A text box expression of:

=count(DISTINCT Customer & Year & Product) should give you the number of combinations, therefore the number of rows in any table using those three Dimensions.

Since you are using a dynamic table, you will have to build the count() expression dynamically.

-Rob

Re: how to count rows in dynamic pivot table

gamaplast — Fri, 29 Dec 2017 15:18:18 GMT

Hi Rob,

what about if I have a table with e conditional expression which limits the rows to be displayed?

For example:

=If(Aggr(Sum(MyValues), MyCode)<0, MyCode, Null()) and "Suppress null values" checkbox marked in the dimension

If(Sum(MyValues)<0, Sum(MyValues)) in the expression?

The count (distinc(MyCode)) shows all possibile values and not only the displayed ones....

Thanks.

Re: how to count rows in dynamic pivot table

pradosh_thakur — Fri, 29 Dec 2017 15:35:09 GMT

looking for something like this

count (distinct dim1,dim2...) if you have fixed dimensions

if they are populated from a list box than

if(getselectedcount(YourDimensionListbox)>0,count(distinct $(=concat('[' & YourDimensionListbox & ']', '&'))),0)

Re: Add a row no field to a straight table

ps: there may be typo in the expression

Re: how to count rows in dynamic pivot table

rwunderlich — Tue, 02 Jan 2018 18:49:05 GMT

I think it's:

count( If(Aggr(Sum(MyValues), MyCode)<0, MyCode, Null()))

-Rob