topic Re: Set Analysis with nested Replace function in App Development

Set Analysis with nested Replace function

njimack — Wed, 14 Oct 2020 16:16:23 GMT

The below code is giving me the following error: "Error in set modifier ad hoc element list. ',' or ')' expected.

I think it's because of the Replace function, so my question is, do I have the syntax wrong, or is there a better way of doing this.

This expression is to be used in a table - if TM1_Account contains the string "YTD", then I want the expression to be calculated using "MTD".

=if(Account_Category = 'Revenue',

sum({<IsInYTD = {1},

CYTDFlag = {1},

Version = {'Actual'}>}

(Amount))/1000000,

if(Account_Category = 'PE Balance Sheet',

sum({<IsInYTD= {1},

CYTDFlag = {1},

IsCurrentMonth = {1},

Version = {'Actual'}>}

(Amount)))/1000000000,

sum({<IsInYTD={1},

CYTDFlag={1},

IsCurrentMonth={1},

TM1_Account={Replace(TM1_Account,'YTD','MTD')},

Version = {'Actual'}>}

(Amount))/1000000000)

Re: Set Analysis with nested Replace function

Taoufiq_Zarra — Wed, 14 Oct 2020 20:59:36 GMT

@njimack try this

=if(Account_Category = 'Revenue', sum({<IsInYTD = {1}, CYTDFlag = {1}, Version = {'Actual'}>} (Amount))/1000000, if(Account_Category = 'PE Balance Sheet', sum({<IsInYTD= {1}, CYTDFlag = {1}, IsCurrentMonth = {1}, Version = {'Actual'}>} (Amount))/1000000000, sum({<IsInYTD={1}, CYTDFlag={1}, IsCurrentMonth={1}, TM1_Account={Replace(TM1_Account,'YTD','MTD')}, Version = {'Actual'}>} (Amount))/1000000000))

Re: Set Analysis with nested Replace function

njimack — Thu, 15 Oct 2020 07:52:25 GMT

Thanks, but it's still giving me the same error message.

Re: Set Analysis with nested Replace function

Kushal_Chawda — Thu, 15 Oct 2020 10:07:21 GMT

@njimack not sure why you need replace here. you can directly call "MTD" instead

=if(Account_Category = 'Revenue', sum({<IsInYTD = {1}, CYTDFlag = {1}, Version = {'Actual'}>} (Amount))/1000000, if(Account_Category = 'PE Balance Sheet', sum({<IsInYTD= {1}, CYTDFlag = {1}, IsCurrentMonth = {1}, Version = {'Actual'}>} (Amount)))/1000000000, sum({<IsInYTD={1}, CYTDFlag={1}, IsCurrentMonth={1}, TM1_Account={'MTD'}, Version = {'Actual'}>} (Amount))/1000000000)

Re: Set Analysis with nested Replace function

njimack — Thu, 15 Oct 2020 10:33:55 GMT

It's not that simple unfortunately. The account could be one of the below 4 items, and the list of potential accounts might expand over time so I don't want to hard-code anything.

For Balance Sheet items, I want the calculation to be performed only on the MTD value, not the YTD value.

Avg Liabilities (MTD)
Avg Liabilities (YTD)
Avg Assets (MTD)
Avg Assets (YTD)

Re: Set Analysis with nested Replace function

Kushal_Chawda — Thu, 15 Oct 2020 10:40:48 GMT

@njimack try below then

=if(Account_Category = 'Revenue', sum({<IsInYTD = {1}, CYTDFlag = {1}, Version = {'Actual'}>} (Amount))/1000000, if(Account_Category = 'PE Balance Sheet', sum({<IsInYTD= {1}, CYTDFlag = {1}, IsCurrentMonth = {1}, Version = {'Actual'}>} (Amount))/1000000000, sum({<IsInYTD={1}, CYTDFlag={1}, IsCurrentMonth={1}, TM1_Account={"=Replace(TM1_Account,'YTD','MTD')"}, Version = {'Actual'}>} (Amount))/1000000000))

Re: Set Analysis with nested Replace function

njimack — Thu, 15 Oct 2020 12:56:18 GMT

Thanks for your help - this doesn't cause an error but it just returns zero values for every row of the table.

I've decided to go another way and used LIKE to test whether or not TM1_Account contains "YTD". If it does, I'm simply returning zero.

Re: Set Analysis with nested Replace function

sunny_talwar — Thu, 15 Oct 2020 13:08:42 GMT

@njimack how about this... assuming you select a single value in TM1_Account...

=if(Account_Category = 'Revenue', Sum({<IsInYTD = {1}, CYTDFlag = {1}, Version = {'Actual'}>} (Amount))/1000000, if(Account_Category = 'PE Balance Sheet', sum({<IsInYTD= {1}, CYTDFlag = {1}, IsCurrentMonth = {1}, Version = {'Actual'}>} (Amount))/1000000000, sum({<IsInYTD={1}, CYTDFlag={1}, IsCurrentMonth={1}, TM1_Account={"$(=Replace(TM1_Account,'YTD','MTD'))"}, Version = {'Actual'}>} (Amount))/1000000000))

Re: Set Analysis with nested Replace function

QFabian — Thu, 15 Oct 2020 13:15:11 GMT

hi, you are doing 'if(sum(' , try doing sum( if(' instead.

Or try adding a 'sum(aggr(' grouping all your expression.