Expand my Community achievements bar.

SOLVED

Creating a calculated column from calculated fields

Avatar

Level 1

I am trying to create a calculated column in a task report that sums a number of calculated fields.

The fields are in Currency format and some may contain null values. 

I have tried the following code with no result:

displayname=Y1 Profit Total
textmode=true
valueexpression=(IF(ISBLANK({DE:PD - AA EA Y1 GP $}, "", 0, {DE:PD - AA EA Y1 GP $}) + IF(ISBLANK({DE:PD - AA MPK Y1 GP $}, "", 0, {DE:PD - AA MPK Y1 GP $}) + IF(ISBLANK({DE:PD - AA CAR Y1 GP $}, "", 0, {DE:PD - AA CAR Y1 GP $}) + IF(ISBLANK({DE:PD - BB EA Y1 GP $}, "", 0, {DE:PD - BB EA Y1 GP $}) + IF(ISBLANK({DE:PD - BB MPK Y1 GP $}, "", 0, {DE:PD - BB MPK Y1 GP $}) + IF(ISBLANK({DE:PD - BB CAR Y1 GP $}, "", 0, {DE:PD - BB CAR Y1 GP $}) + IF(ISBLANK({DE:PD - CC EA Y1 GP $}, "", 0, {DE:PD - CC EA Y1 GP $}) + IF(ISBLANK({DE:PD - CC MPK Y1 GP $}, "", 0, {DE:PD - CC MPK Y1 GP $}) + IF(ISBLANK({DE:PD - CC CAR Y1 GP $}, "", 0, {DE:PD - CC CAR Y1 GP $}))
valueformat=currencyString

 

Any help would be amazing, thank you.

Topics

Topics help categorize Community content and increase your ability to discover relevant content.

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

I don't think I've ever seen null values being a problem that one would have to workaround. Did you try a simple sum? SUM({DE:field1},{DE:field2},{DE:field3}, and so on...)?

View solution in original post

2 Replies

Avatar

Correct answer by
Community Advisor

I don't think I've ever seen null values being a problem that one would have to workaround. Did you try a simple sum? SUM({DE:field1},{DE:field2},{DE:field3}, and so on...)?

Avatar

Level 1

Thank you Skye! Worked a treat. Well that was ChatGPT sending me on a wild goose chase.

The complete code for anyone interested is:

 

displayname=Y1 Profit Total
textmode=true
valueexpression=(SUM({DE:field1},{DE:field2},{DE:field3}))
valueformat=currencyString