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

Limit # of Rows in Chart (Pivot Table)

Mon, 18 Aug 2014 14:07:23 GMT

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)

Gysbert_Wassenaar — Mon, 18 Aug 2014 14:19:29 GMT

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

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

Mon, 18 Aug 2014 14:21:51 GMT

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

-Ram

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

Mon, 18 Aug 2014 15:04:50 GMT

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

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

Mon, 18 Aug 2014 15:31:15 GMT

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)

Gysbert_Wassenaar — Mon, 18 Aug 2014 15:39:30 GMT

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

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

antoniotiman — Mon, 18 Aug 2014 15:56:47 GMT

Hi Mark,

try like

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

Regards,

Antonio

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

Mon, 18 Aug 2014 17:08:13 GMT

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)

antoniotiman — Tue, 19 Aug 2014 08:30:42 GMT

Hi Mark,

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

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

Tue, 19 Aug 2014 11:35:22 GMT

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

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

etc

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

antoniotiman — Tue, 19 Aug 2014 13:26:11 GMT

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)

Tue, 19 Aug 2014 14:05:29 GMT

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)

antoniotiman — Tue, 19 Aug 2014 14:19:57 GMT

Hi,

If I understant You want limit of Dim to 10.

In Condition add

and Count(DISTINCT Dim) <= 10

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

Tue, 19 Aug 2014 14:43:49 GMT

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

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

antoniotiman — Tue, 19 Aug 2014 15:21:52 GMT

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)

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

Tue, 19 Aug 2014 15:42:28 GMT

Hopefully this attachment works.

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

antoniotiman — Tue, 19 Aug 2014 16:18:15 GMT

Mark,

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

You replace with

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

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

chriys1337 — Fri, 08 Dec 2017 15:18:57 GMT

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?

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

jhamard — Wed, 13 Dec 2017 14:23:28 GMT

It works but eat a a lot of CPU.

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

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

jhamard — Tue, 16 Jan 2018 13:03:53 GMT

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 ...