
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]))
