Convert a nested if expression into set analysis

[#1]

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

[rwunderlich · Accepted solution]

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

[#3]

Hello Rob

This expression is working perfectly.

How do I convert this to set analysis now?

Thanks

[#4]

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)

[rwunderlich]

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

http://masterssummit.com

[#6]

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

[rwunderlich]

It doesn't look half-bad as it is. Are you having trouble with it?

-Rob

[#8]

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?