topic Re: Calculation Condition - Complex in QlikView

Calculation Condition - Complex

Thu, 03 Jan 2013 09:34:22 GMT

Hi Folks

Happy New Year to everyone.....

I have a problem with a table where I have a number of conditional dimensions, and I'd like to only calculate the table when the resulting number of rows to display inside the table is less than 250.

I cannot simply limit this by looking at a row counter in the database, because if the user decides to only show Region as a dimension then this would result in only 4 rows being shown in the table and in this case I'd obviously like to show all of the data against those 4 rows. However, if the user decides to show 10 dimensions and this results in a table that would provide 10 million rows, I would like to be able to activate the conditional calculation error message.

I'm assuming that this is going to need to be done by using some form of Aggr function, but I'm struggling to know where to start with this.

For information, the dimensions are conditionally shown using the following formula:

=SubStringCount(Concat(%DimensionName, '|'), 'Book Date')

If anybody can give me any pointers as to how I get the calculation condition working I would really appreciate it.

PS: I do not want to use a macro to do this.

Thanks,

Nigel.

Re: Calculation Condition - Complex

Gysbert_Wassenaar — Thu, 03 Jan 2013 09:54:16 GMT

You have to aggregate the count over all the dimensions in your chart. Something like this:

count(aggr(count(1),Dim1,Dim2,Dim3....,DimX)

Re: Calculation Condition - Complex

Thu, 03 Jan 2013 09:59:31 GMT

Thanks for the response.

My question then is, can I do the list of dimensions in the Aggr statement as a Concat function?

For example:

Count(Aggr(Count(1),Concat(%DimensionName,',')))

Re: Calculation Condition - Complex

Gysbert_Wassenaar — Thu, 03 Jan 2013 10:18:23 GMT

If %DimensionName is a field to select dimensions I don't think you can use the concat expression directly. But you can use a variable to hold the concat expression =concat('['&%DimensionName&']',',') and use the variable in the count expression count(Aggr(count(1),$(vDims))). See attached example