Simplify the below expression

[#1]

Hello

Can someone 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

[#2 · Accepted solution]

Hi,

Try with below:

sum(aggr(pick(match([Currency],'Transactional','Functional','Reporting') ,$(vf-ContingencyPersuasiveEvidence),

  $(vf-ContingencyPersuasiveEvidence) * max({<[Revenue Schedule Type]= {'RPRO-REVENUE'}>} [EX Rate]),

  $(vf-ContingencyPersuasiveEvidence) * max({<[Revenue Schedule Type]= {'RPRO-REVENUE'}>} [EX Rate]) *

  max({<[Revenue Schedule Type]= {'RPRO-REVENUE'}>} [Reporting Currency Ex Rate]) ),[Transaction ID]))

else, use another 2 variables to store Max(Ex-Rate) and max(Reporting currency ex rate) and then try to replace them in above expression.

[#3]

Hello Venu

This works fine. Thanks a lot. But if I have null or not is null condition how would I put that into set or more simplified manner.

For example

How would you simplify the below expression

sum(aggr(If([Currency]='Transactional' and (not isnull(ATTRIBUTE48) OR not isnull(ATTRIBUTE49) OR not isnull(ATTRIBUTE50) OR not isnull(ATTRIBUTE51) OR not isnull(ATTRIBUTE52) OR not isnull(ATTRIBUTE36)) ,[Price List],

If

([Currency]='Functional' and (not isnull(ATTRIBUTE48) OR not isnull(ATTRIBUTE49) OR not isnull(ATTRIBUTE50) OR not isnull(ATTRIBUTE51) OR not isnull(ATTRIBUTE52) OR not isnull(ATTRIBUTE36)),

    [Price List] * max(if([Revenue Schedule Type]='RPRO-COGS',

                   [EX Rate]),

                   0

    ),

    If

([Currency]='Reporting' and (not isnull(ATTRIBUTE48) OR not isnull(ATTRIBUTE49) OR not isnull(ATTRIBUTE50) OR not isnull(ATTRIBUTE51) OR not isnull(ATTRIBUTE52) OR not isnull(ATTRIBUTE36)),

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

[#4]

Hello venugopal4s

How would you simplify the below expression or convert it into set analysis?

sum(aggr(If([Currency]='Transactional' and (not isnull(ATTRIBUTE48) OR not isnull(ATTRIBUTE49) OR not isnull(ATTRIBUTE50) OR not isnull(ATTRIBUTE51) OR not isnull(ATTRIBUTE52) OR not isnull(ATTRIBUTE36)) ,[Price List],

If

([Currency]='Functional' and (not isnull(ATTRIBUTE48) OR not isnull(ATTRIBUTE49) OR not isnull(ATTRIBUTE50) OR not isnull(ATTRIBUTE51) OR not isnull(ATTRIBUTE52) OR not isnull(ATTRIBUTE36)),

    [Price List] * max(if([Revenue Schedule Type]='RPRO-COGS',

                   [EX Rate]),

                   0

    ),

    If

([Currency]='Reporting' and (not isnull(ATTRIBUTE48) OR not isnull(ATTRIBUTE49) OR not isnull(ATTRIBUTE50) OR not isnull(ATTRIBUTE51) OR not isnull(ATTRIBUTE52) OR not isnull(ATTRIBUTE36)),

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

[#5]

Hi,

I tried my level best. See the below expression whether it can help you or not.

create a variable and assign the expression.

vPriceList = Only({<ATTRIBUTE48 -= {'$(=Null())'}, ATTRIBUTE49 -= {'$(=Null())'},

                             ATTRIBUTE50 -= {'$(=Null())'}, ATTRIBUTE51 -= {'$(=Null())'},

                             ATTRIBUTE52 -= {'$(=Null())'}, ATTRIBUTE36 -= {'$(=Null())'} >} [Price List]);

Use the variable in your expression.

sum(aggr(pick(match([Currency],'Transactional','Functional','Reporting') , vPriceList,

  vPriceList * max({<[Revenue Schedule Type]= {'RPRO-COGS'}>} [EX Rate]),

  vPriceList * max({<[Revenue Schedule Type]= {'RPRO-COGS'}>} [EX Rate]) *

  max({<[Revenue Schedule Type]= {'RPRO-COGS'}>} [Reporting Currency Ex Rate]) ),[Transaction ID]))

[#6]

Hello Venu

I have included the null conditions in the bac end of my script as a dummy dimension.

Now my expression looks like this

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

Can we simplify this as set analysis?