Limit # of Rows in Chart (Pivot Table)

[#1]

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!

[Gysbert_Wassenaar]

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

[#3]

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

-Ram

[#4]

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

[#5]

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.

[Gysbert_Wassenaar]

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

[antoniotiman · Accepted solution]

Hi Mark,

try like

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

Regards,

Antonio

[#8]

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.

[antoniotiman]

Hi Mark,

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

[#10]

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

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

etc