Set analysis - Field = Field

[Anonymous]

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

[maxgro]

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

[sunny_talwar]

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)

[Anonymous]

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.

[maxgro]

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