Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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!

19 Replies
antoniotiman
Master III
Master III

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

Not applicable
Author

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.

antoniotiman
Master III
Master III

Hi,

If I understant You want limit of Dim to 10.

In Condition add

and Count(DISTINCT Dim) <= 10

Not applicable
Author

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.

Edit: removed bad attachment

antoniotiman
Master III
Master III

Mark,

Your attachment is corrupted.

However, in calculated condition

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

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

Not applicable
Author

Hopefully this attachment works.

antoniotiman
Master III
Master III

Mark,

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

You replace with

Count(Aggr(1,$(=Concat('['&AdhocFields&']',',')))) < 100000

chriys1337
Creator III
Creator III

Hello gwassenaar ,

could you please explain what your formula is doing?

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?

jhamard
Partner - Contributor III
Partner - Contributor III

It works but eat a a lot of CPU.

Need a simple function like GetChartRowCount('CHARTID') ...

jhamard
Partner - Contributor III
Partner - Contributor III

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

It works when you have only one measure (or if all your measure can be aggregated at the same level).

It's complicated to explain to my client that there is no way to count the exact number of rows in my dynamic table ...