Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

If Else statement into Set Analysis

Hello,

How can I rewrite the following If Else statement using Set Analysis?

I am trying to display the variables [ABC admin target], [ABC client target], and [ABC internal target] (user entered input in an Input Box) next to the according employee/project category in a straight table.

I got the first part to work by doing:

sum({$<[Last Name] ={"ABC"}, [Project Code Type]={"Admin"}>} [ABC admin target] )

but I am not sure how I can write the Else statement using Set Analysis.

Here's the If Else Statement:

if([Last Name]='ABC' and [Project Code Type]='Admin',[ABC admin target],

if([Last Name]='ABC' and [Project Code Type]='Client',[ABC client target],

if([Last Name]='ABC' and [Project Code Type]='Internal',[ABC internal target]))

Thanks, Maria



3 Replies
johnw
Champion III

Maybe do the same for the others and add them together? I also recommend single quotes instead of double quotes for literals. Double quotes specify a search string, which while it returns the exact same results, might take a different and slightly less efficient path through the system. So single quotes for literals, double quotes for search strings.

sum({<[Last Name]={'ABC'},[Project Code Type]={'Admin'} >} [ABC admin target])
+sum({<[Last Name]={'ABC'},[Project Code Type]={'Client'} >} [ABC client target])
+sum({<[Last Name]={'ABC'},[Project Code Type]={'Internal'}>} [ABC internal target])

However, if those are the ONLY three project code types, this might be just as efficient, possibly even better (on the down side, to me, the above is more clear):

sum({<[Last Name]={'ABC'}>}
pick(match([Project Code Type],'Admin','Client','Internal')
,[ABC admin target],[ABC client target],[ABC internal target]))

Not applicable
Author

Actually, I did try that, but it doesn't work. I think it may have to do with the fact that I don't actually need to sum up these variables, but in order to use Set Analysis, I need to do some kind of summation or aggregation, right?

Maria

johnw
Champion III

If you don't want an aggregation, then you don't want set analysis. You COULD use only() where I used sum() to force it to work, but I think it will actually be less efficient than what you started with. I'd stick with your nested if(), or replace it with this less clear but more compact version:

if([Last Name]='ABC'
,pick(match([Project Code Type],'Admin','Client','Internal')
,[ABC admin target],[ABC client target],[ABC internal target]))