topic Dynamic set analysis in QlikView

Dynamic set analysis

Tue, 05 Jan 2010 10:08:27 GMT

Hi,

I'm currently building a QV application, where each line in a straight table must use different bookmarks for the set analysis.

In my small sample I use the following expression - which works perfectly:

if (Line=1, sum({BM1} Amount),
if (Line=2, sum({BM2} Amount),
if (Line=3, sum({BM3} Amount),
-1)))

However, this expression is not very scalable, and I do not know the number of lines in advance.

I have tried re-writing this to something like this:

sum({$(=concat('BM0' & Line))} Amount)

But it doesn't seem to work. Any ideas?

Best regards

Steen

Dynamic set analysis

Tue, 05 Jan 2010 11:10:16 GMT

Hi Steen,

I've tried upload a file but there is an error in the server, so, I write the solution:

Create a variable: v_field = 'BM' & Max(Linea)

The expression: =Sum({$(#v_field)} Amount )

I hope this help you.

Best regards.

Dynamic set analysis

Tue, 05 Jan 2010 21:33:19 GMT

Thanks for you quick response.

Unfortunately it doesn't seem to do the trick (unless I misunderstood your advice).

I have made a small sample of the problem showing the expected result and a second area trying to use your advice with variables.

Best regards

Steen

Dynamic set analysis

mike_garcia — Tue, 05 Jan 2010 21:54:25 GMT

Hi,

I would suggest to somehow associate the Field 'Line' To the Transactions table and continue using it as Dimension in your Straight table. If you do that, there would be no need to use Set Analysis.

Just a thought.

Mike

Dynamic set analysis

Tue, 05 Jan 2010 22:32:29 GMT

Thanks for your suggestion. However, it is not possible to do that. My sample is "boil down" of the actual problem and there is no way that I can associate the "Line"-field with the transactions table.

But thanks anyway!

/Steen

Dynamic set analysis

Oleg_Troyansky — Wed, 06 Jan 2010 07:26:01 GMT

Steen,

the specific problem that you presented might not have the solution that you are looking for - mainly because Set Analysis expressions, naturally, are evaluated over the full data set, and they can't be calculated based on the line numbers.

I'm questioning your initial statement that locked you into a problem with no solution:


SteenOlesen wrote:I'm currently building a QV application, where each line in a straight table must use different bookmarks for the set analysis.

Perhaps you could come up with a different technique that solves your business problem, except for using Bookmarks?