4 Replies Latest reply: Mar 12, 2018 5:42 PM by Massimo Grossi

# Set analysis - Field = Field

Hi,

I am struggling to convert an IF THEN expression to Set Analysis.

I have a sample below.

Original expression:

sum( if(YM = EFFECTIVEYM, Sales))

Set Anaysis expression:

sum( {\$< YM = P(EFFECTIVEYM)  >} Sales)

This set analysis works fine only of there is 1 YM value selected. As soon as I select more than 1 YM value, then the figures are overstated.

Is there another way of writing set analysis where FIELD = FIELD?

Thanks

Yatin

• ###### Re: Set analysis - Field = Field

maybe

here, page 14, Using two fields

Set Analysis: syntaxes, examples

or you can add a flag in the script and use that flag in set analysis

• ###### Re: Set analysis - Field = Field

Hi,

I have reviewed this pdf, but don't understand what it means by creating an autonumber() key? How do I create this? Is it something I need to do in the backend script?

Also, these 2 fields are in separate tables, so I cannot create a Flag field in the backend.

"Attention: the searched dimension cannot be also in the boolean condition. If needed, create an integer key with Autonumber(). "

Thanks.

• ###### Re: Set analysis - Field = Field

1)

The pdf suggestion works if the flelds are in the same table and

you have a primary key in the table; if not, may be you can create in the script a primary key with the autonumber function

2)

Also, these 2 fields are in separate tables, so I cannot create a Flag field in the backend.

Sometimes is possible if there is a relation between the 2 tables

ATable

AKey,     YM

1,          1-1-2018

2,          2-1-2018

BTable

AKey,   BKey,     EFFECTIVEYM,     Flag

1,         a,           10-1-2018,

1,         b,           1-1-2018,               1     the ATable YM  of AKey 1 is 1-1-2018 = EFFECTIVEYM

2,         c,           1-1-2017,

2,         d,           1-1-2017,

applymap or a join can be useful to calculate the flag

3)

sum if can be a problem with many rows (performance)

4)

maybe you can post your .qvw or a small example

• ###### Re: Set analysis - Field = Field

Do they come from the same table in the back end? If they do... may be create a flag in the script

If(YM = EFFECTIVEYM, 1, 0) as YMFlag

and then this

Sum({\$<YMFlag = {'1'}>} Sales)