topic Re: Set Analysis Expression in QlikView

Set Analysis Expression

tmumaw — Tue, 23 Apr 2013 11:27:57 GMT

Good morning everyone. Sort of confused on this one. The "if" statement works, but the set analysis does not. Does anyone see anything that would make it not work? Would love to use set analysis

Thanks

sum(if([Profit Center] = [Hist Profit Center] and YTDFlag = '1',LID)) (WORKS)

sum({<[Profit Center] = {“$(=trim([Hist Profit Center]))”},FISPD=,YTDFlag = {'1'}>} [LID]) (DON'T WORK)

sum({<[Profit Center] = {[Hist Profit Center]},FISPD=,YTDFlag = {1}>} [LID]) (DON'T WORK)

Re: Set Analysis Expression

jagan — Tue, 23 Apr 2013 11:45:13 GMT

Hi,

Try like this

=sum({<[Profit Center] = [Hist Profit Center],FISPD=,YTDFlag = {'1'}>} [LID])

Hope this helps you.

Regards,

Jagan.

Re: Set Analysis Expression

alexandros17 — Tue, 23 Apr 2013 11:47:06 GMT

Set analysis doesn't work if you compare a field value with another field value, the right expression must be a value (a number, string or variable) or at least an aggregation of a field

Re: Set Analysis Expression

tmumaw — Tue, 23 Apr 2013 11:47:22 GMT

Thanks....but that does not work either.

Thom

Re: Set Analysis Expression

jagan — Tue, 23 Apr 2013 11:51:01 GMT

Hi,

Try this

=sum({<[Profit Center] ={$(=Concat(DISTINCT [Hist Profit Center], ','))},FISPD=,YTDFlag = {'1'}>} [LID])

http://community.qlik.com/docs/DOC-4021

Hope this works.

Regards,

Jagan.

Re: Set Analysis Expression

tmumaw — Tue, 23 Apr 2013 12:00:38 GMT

Not yet Jagan....

Re: Set Analysis Expression

kaushiknsolanki — Tue, 23 Apr 2013 12:06:13 GMT

Try this.

sum({<[Profit Center] = {“=[Hist Profit Center] = [Profit Center]”},FISPD=,YTDFlag = {'1'}>} [LID])

Else upload your qvw file.

Regards,

Kaushik Solanki

Re: Set Analysis Expression

danielrozental — Tue, 23 Apr 2013 12:20:43 GMT

Alexandros is right, set analysis will only get evaluated once, before the object is calculated. It's not possible to obtain different values for different rows.

This will work if there is a single possible value for Hist Profit Center

sum({<[Profit Center] = {'$(=only([Hist Profit Center]))'},FISPD=,YTDFlag = {1}>} [LID])

Else, you need to do something like this

sum({<FISPD=,YTDFlag = {1}>} if([Profit Center] = [Hist Profit Center],[LID]))

Re: Set Analysis Expression

tmumaw — Tue, 23 Apr 2013 12:20:48 GMT

Sorry Kaushik....but Nope.

Re: Set Analysis Expression

kuba_michalik — Tue, 23 Apr 2013 13:24:58 GMT

Do you want to "copy" selections from one field to another, or sum only those records which have value of [Profit Center] equal to [Hist Profit Center] (which is what the If does)? The latter is undoable with set analysis, as far as I know.

(You are not comparing values, but defining selection for [Profit Center], so if one value in [Profit Center] exists both with same [Hist Profit Center] and with a different [Hist Profit Center], there is no way to define a selection on [Profit Center] that would filter out only the values you want).

Re: Set Analysis Expression

CELAMBARASAN — Tue, 23 Apr 2013 13:49:51 GMT

Hi,

I hope that you have unique key for the table

sum({<[UniqueKeyField] = {“=[Hist Profit Center] = [Profit Center]”},FISPD=,YTDFlag = {'1'}>} [LID])

Hope it helps

Celambarasan