Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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]))
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]))
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]))
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?