Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
tmumaw
Specialist II
Specialist II

Set Analysis Expression

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)

10 Replies
jagan
Luminary Alumni
Luminary Alumni

Hi,

Try like this

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

Hope this helps you.

Regards,

Jagan.

alexandros17
Partner - Champion III
Partner - Champion III

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

tmumaw
Specialist II
Specialist II
Author

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

Thom

jagan
Luminary Alumni
Luminary Alumni

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.

tmumaw
Specialist II
Specialist II
Author

Not yet Jagan....

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Try this.

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

Else upload your qvw file.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
danielrozental
Master II
Master II

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]))

tmumaw
Specialist II
Specialist II
Author

Sorry Kaushik....but Nope.

kuba_michalik
Partner - Specialist
Partner - Specialist

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).