Set Analysis with nested Replace function

[njimack]

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)

[Taoufiq_Zarra]

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

[njimack]

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

[Kushal_Chawda]

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

[njimack]

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)

[Kushal_Chawda]

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

[njimack]

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.

[sunny_talwar]

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

[QFabian]

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

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