19 Replies Latest reply: Jan 16, 2018 8:03 AM by Julien HAMARD

# Limit # of Rows in Chart (Pivot Table)

Hi everyone,

I have a Pivot Table in which the dimensions are conditionally enabled. The user selects from a list of dimensions and then the chart 'builds' itself just like a traditional pivot table in Excel. Dimensions can carry a varying level of granularity so I would like to have a calculated condition on the chart so that the user doesn't create a pivot with say more than 100K rows of data returned.   Is there a way to set this calc condition so that the chart won't display if the combination of user selected dimensions and expressions would result in a chart with >100K rows?

Thanks!

• ###### Re: Limit # of Rows in Chart (Pivot Table)

Try something like if(sum(aggr(1, DimA, DimB, ..., DimX)) < 100000,1,0).

• ###### Re: Re: Limit # of Rows in Chart (Pivot Table)

Hi Gysbert - this method works but only if I explicitly state the dimension, e.g Dim1, Dim2, Dim3.  The method I'm trying to develop sets a variable and I could not get your suggestion to work using if(sum(aggr(1,vDim1, vDim2, vDim3))<100000,1,0).

For example, if I have 20 possible dimensions to choose from but only 10 open variable slots - vDim1, vDim2,...,vDim10.  This is so the user can pick at most 10 dimensions.  So I think for your method to work I'd have to use  if(sum(aggr(1,Dim1, Dim2, Dim3,...Dim20))<100000,1,0) - essentially list every single possible dimension in the aggr.   I will have 100+ possible dimensions in the list that the user can select so that'd get very lengthy.

• ###### Re: Re: Limit # of Rows in Chart (Pivot Table)

Well, if you find an alternative (that doesn't involve macro's!), please let us know.

• ###### Re: Limit # of Rows in Chart (Pivot Table)

Hello gwassenaar ,

sum(aggr(1, DimA, DimB, ..., DimX)

I tried the same with count() and it is the same result. Also I am not understanding why do you put after aggr( first of all a "1". Without the 1 the formula is not working.

And what does the result of the whole formula tell me?

• ###### Re: Limit # of Rows in Chart (Pivot Table)

Try with Rank() function. Can you provide sample data file.

-Ram

• ###### Re: Re: Limit # of Rows in Chart (Pivot Table)

Here is an example.  Let's say I do not want this pivot to calculate if the # of rows exceeds 10.

• ###### Re: Limit # of Rows in Chart (Pivot Table)

Hi Mark,

try like

Count(Aggr(1,\$(=Concat(Dim,',')))) < 1000000

Regards,

Antonio

• ###### Re: Limit # of Rows in Chart (Pivot Table)

Antonio - do you have any suggestion on getting this to work with a variable?  I've tried to change the =Concat(Dim...) to reference the my variable fields but cannot get the string to evaluate. My application is struggling to concatenate all of the Dim fields together (I have over 100).  I have 10 variable slots, vDim1 through vDim10, that the user can select from the list of 100 possible dimensions.

• ###### Re: Limit # of Rows in Chart (Pivot Table)

Hi Mark,

what is the value of vDim1,...,vDim10 ?

• ###### Re: Limit # of Rows in Chart (Pivot Table)

vDim1: =subfield(concat(Dim,' | '),' | '),1)

vDim2: =subfield(concat(Dim,' | '),' | '),2)

etc

• ###### Re: Limit # of Rows in Chart (Pivot Table)

Hi Mark,

all the values of Dim are already in Concat(Dim,',').

When I select Pick Dimensions, values of Dim (Dim1,Dim2,...,Dimn)

are in Concat.

Regards,

Antonio

• ###### Re: Limit # of Rows in Chart (Pivot Table)

Hi Antonio - yes, that is correct, however in my real application I will have over 100 unique dims.  When I try to concatenate all of those dim fields together my application runs out of memory (on a 64bit PC).  This is why I am looking to adapt the string to use a variable since I've restricted that number to 10 possible dimension selections.

• ###### Re: Limit # of Rows in Chart (Pivot Table)

Hi,

If I understant You want limit of Dim to 10.

and Count(DISTINCT Dim) <= 10

• ###### Re: Re: Limit # of Rows in Chart (Pivot Table)

Antonio - perhaps I am misinterpreting your suggestion.  Please see the attached demo of my app.  There are 130 dimensions the user can select from and I'd like to limit them to only selecting 10 dimensions.  And back to the original question, I am trying to set a calculated condition so that the resulting pivot table does not exceed 100K rows.

• ###### Re: Limit # of Rows in Chart (Pivot Table)

Mark,

However, in calculated condition

Count(Aggr(1,\$(=Concat(Dim,',')))) < 100000             (Limit 100000  Rows)

and Count(DISTINCT Dim) <= 10                               (Limit selected Dimensions   to 10)

• ###### Re: Re: Limit # of Rows in Chart (Pivot Table)

Hopefully this attachment works.

• ###### Re: Limit # of Rows in Chart (Pivot Table)

Mark,

It don't work because of space in values of field AdhocFields.

You replace with