Dynamic set analysis

[#1]

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

[#2]

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.

[#3]

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

[mike_garcia]

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

[#5]

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

[Oleg_Troyansky]

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?