topic Re: Set analysis - Field = Field in QlikView

Set analysis - Field = Field

Anonymous — Fri, 09 Mar 2018 09:21:55 GMT

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

maxgro — Fri, 09 Mar 2018 16:47:08 GMT

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

sunny_talwar — Fri, 09 Mar 2018 16:49:16 GMT

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)

Re: Set analysis - Field = Field

Anonymous — Mon, 12 Mar 2018 10:50:08 GMT

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

maxgro — Mon, 12 Mar 2018 21:42:08 GMT

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

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

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

maybe you can post your .qvw or a small example