Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello
I am trying to convert a nested if expression into a set expression. Any inputs on this would be appreciated.
Here is the expression
If([Currency]='Transactional',
Sum(If( [GL Date]>= $(=[StartPeriod]) AND [GL Date]<= $(=[EndPeriod]) and
([Revenue Type]='DEF' or [Revenue Type]='CLIB') AND [Forecast Flag]='N' and (IsNull([Schedules ATTRIBUTE1]) or [Schedules ATTRIBUTE1] = 'CM') and [Schedule On Hold]='N' and ATTRIBUTE44<>'OPEN_BAL_CONV',[Net Amount])),
if([Currency]='Functional',
Sum(If( [GL Date]>= $(=[StartPeriod]) AND [GL Date]<= $(=[EndPeriod]) and
([Revenue Type]='DEF' or [Revenue Type]='CLIB') and [Forecast Flag]='N' and (IsNull([Schedules ATTRIBUTE1]) or [Schedules ATTRIBUTE1] = 'CM') and [Schedule On Hold]='N' and ATTRIBUTE44<>'OPEN_BAL_CONV',[Net Amount Operating Currency])),
if([Currency]='Reporting',
Sum(If( [GL Date]>= $(=[StartPeriod]) AND [GL Date]<= $(=[EndPeriod]) and
([Revenue Type]='DEF' or [Revenue Type]='CLIB') and [Forecast Flag]='N' and (IsNull([Schedules ATTRIBUTE1]) or [Schedules ATTRIBUTE1] = 'CM') and [Schedule On Hold]='N' and ATTRIBUTE44<>'OPEN_BAL_CONV',[Net Amount Reporting Currency])),0
// Sum (If(([GL Date]>= $(=[StartPeriod]) AND [GL Date]<= $(=[EndPeriod]))and [Initial Entry Flag]='Y',[Net Amount],0))
)
)
)
Thanks
For starters, it looks like the only difference between the if() is which field gets SUMmed? If so, it can be simplified to:
Sum(If( [GL Date]>= $(=[StartPeriod]) AND [GL Date]<= $(=[EndPeriod]) and
([Revenue Type]='DEF' or [Revenue Type]='CLIB') AND [Forecast Flag]='N' and (IsNull([Schedules ATTRIBUTE1]) or [Schedules ATTRIBUTE1] = 'CM') and [Schedule On Hold]='N' and ATTRIBUTE44<>'OPEN_BAL_CONV'
,alt(pick(Match([Currency], 'Transactional', 'Functional', 'Reporting'),[Net Amount], [Net Amount Operating Currency], [Net Amount Reporting Currency]),0)
,0)
)
If you can confirm that works, I'll help you convert it a set expression.
-Rob
For starters, it looks like the only difference between the if() is which field gets SUMmed? If so, it can be simplified to:
Sum(If( [GL Date]>= $(=[StartPeriod]) AND [GL Date]<= $(=[EndPeriod]) and
([Revenue Type]='DEF' or [Revenue Type]='CLIB') AND [Forecast Flag]='N' and (IsNull([Schedules ATTRIBUTE1]) or [Schedules ATTRIBUTE1] = 'CM') and [Schedule On Hold]='N' and ATTRIBUTE44<>'OPEN_BAL_CONV'
,alt(pick(Match([Currency], 'Transactional', 'Functional', 'Reporting'),[Net Amount], [Net Amount Operating Currency], [Net Amount Reporting Currency]),0)
,0)
)
If you can confirm that works, I'll help you convert it a set expression.
-Rob
Hello Rob
This expression is working perfectly.
How do I convert this to set analysis now?
Thanks
if(sum({<[GL Date]={'>=$(=[StartPeriod])<=$(=[EndPeriod])'},[Revenue Type]={'DEF','CLIB'},[Forecast Flag]={'N'},[Schedules ATTRIBUTE1]={Null(),'CM'},[Schedule On Hold]={'N'},Attribute={"*"}-{'OPEN_BAL_CONV'},[Currency]={'[Currency]'}>},[Net Amount],if(sum({<[GL Date]={'>=$(=[StartPeriod])<=$(=[EndPeriod])'},[Revenue Type]={'DEF','CLIB'},[Forecast Flag]={'N'},[Schedules ATTRIBUTE1]={Null(),'CM'},[Schedule On Hold]={'N'},Attribute={"*"}-{'OPEN_BAL_CONV'},[Currency]={'Transactional',[Net Amount Operating Currency],sum({<[GL Date]={'>=$(=[StartPeriod])<=$(=[EndPeriod])'}>},[Revenue Type]={'DEF','CLIB'},[Forecast Flag]={'N'},[Schedules ATTRIBUTE1]={Null(),'CM'},[Schedule On Hold]={'N'},Attribute={"*"}-{'OPEN_BAL_CONV'},[Currency]={'Reporting'}>}[Net Amount Reporting Currency],0)
After simplifying with the pick(match)), I'm not sure I would convert this to SA unless I had to. I think it reads easier as an if. It's somewhat complicated by selection of nulls and another value in [Schedules ATTRIBUTE1]. Can't tell if I have the syntax right without data, but here goes.
=sum(
{
($-<[Schedules ATTRIBUTE1]={*}> + <[Schedules ATTRIBUTE1]={'CM'}>)
*
<
[GL Date]={">=$(=[StartPeriod])<=$(=[EndPeriod])"}
,[Revenue Type]={'DEF','CLIB'}
,[Forecast Flag]={'N'}
,[Schedule On Hold]={'N'}
,[ATTRIBUTE44]=-{'OPEN_BAL_CONV'}
>
}
alt(
pick(Match([Currency], 'Transactional', 'Functional', 'Reporting')
,[Net Amount], [Net Amount Operating Currency], [Net Amount Reporting Currency])
,0)
)
-Rob
Hello Rob
Can you please advise me on how to simplify the following expression further or write it in a more efficient way
sum(aggr(If([Currency]='Transactional',$(vf-ContingencyPersuasiveEvidence),
If
([Currency]='Functional',
$(vf-ContingencyPersuasiveEvidence) * max(if([Revenue Schedule Type]='RPRO-REVENUE',
[EX Rate]),
0
),
If
([Currency]='Reporting',
($(vf-ContingencyPersuasiveEvidence) * max(if([Revenue Schedule Type]='RPRO-REVENUE',[EX Rate]),0)) *
max(if([Revenue Schedule Type]='RPRO-REVENUE',[Reporting Currency Ex Rate]),0)
,
0
)
)
)
,[Transaction ID]))
Thanks
It doesn't look half-bad as it is. Are you having trouble with it?
-Rob
Hello Rob
No the above one I was able to convert to set.
I am having issue with the following expression
sum(aggr(If([Currency]='Transactional' and [Dummy1]='Y' ,[Price List],
If
([Currency]='Functional' and [Dummy1]='Y',
[Price List] * max(if([Revenue Schedule Type]='RPRO-COGS',
[EX Rate]),
0
),
If
([Currency]='Reporting' and [Dummy1]='Y',
([Price List] * max(if([Revenue Schedule Type]='RPRO-COGS',[EX Rate]),0)) *
max(if([Revenue Schedule Type]='RPRO-COGS',[Reporting Currency Ex Rate]),0)
,
0
)
)
)
,[Transaction ID]))
Is there a way to simplify it?