Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

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!

1 Solution

Accepted Solutions
Highlighted
antoniotiman
Honored Contributor III

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

Hi Mark,

try like

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

Regards,

Antonio

19 Replies
MVP & Luminary
MVP & Luminary

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

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


talk is cheap, supply exceeds demand
Not applicable

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

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

-Ram

Not applicable

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.

Not applicable

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.

MVP & Luminary
MVP & Luminary

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.


talk is cheap, supply exceeds demand
Highlighted
antoniotiman
Honored Contributor III

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

Hi Mark,

try like

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

Regards,

Antonio

Not applicable

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.

antoniotiman
Honored Contributor III

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

Hi Mark,

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

Not applicable

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

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

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

etc