Subtraction on Custom Fields in Report

[JulieHand]

Hi there - can someone help me figure out the following?  I have 2 Custom Fields, and I need to subtract one from the other, but cannot seem to get it to work. 

I created the Custom column for a Report 'Active # of Weeks'; I need to subtract the # of Days on Hold from the Actual Duration (Days) to get this value.  The results I am getting are simply a 'negative' of the number I enter for the # of Days on Hold (i.e., if I enter 2 for # of Days on Hold, I get a '-2' returned).  Please disregard my 'Weeks' in the below, it is another issue, but I really need to get this calculation working and can fix that later.

Below is my formula:

aggregator.namekey=Active # of Weeks
aggregator.valuefield=DE:Active # of Weeks
displayname=Active # of Weeks
linkedname=direct
namekey=Active # of Weeks
querysort=DE:Active # of Weeks
textmode=true
valueexpression=SUB({DE:Actual Duration (Days)}, {DE:# of Days On Hold})
valuefield=Active # of Weeks
valueformat=doubleAsInt

[skyehansen]

without actually testing your valueexpression in any way, the first thing I'm observing is that your field names have a lot of non-alphanumeric characters in them, and I kind of wonder if that's playing into any of the issues you're facing. If it were me, I would probably remove the parens from your first field and the hashtag from your second field, before trusting any result. (here, I am talking about the actual field itself -- not just this report column)

But before you even get started doing that... I am left wondering whether "active # of weeks" is an actual field, or whether you are supposed to subtract 2 numbers in order to get a third number. If it's NOT an actual custom field, why do you have so many references to a DE:Active # of Weeks?

Lastly, I can't think of any reason you would have a valuefield line AND a valueexpression line. Since your original post says that you are trying to subtract one number from the other, just delete the valuefield line, it doesn't have a place here.

[JulieHand]

Hi @sky - thank you so much.  Honestly, I was guessing on some of this, and using some previous formulas.  Thank you for the tips!  I have adjusted a few things, but I cannot change the '# of Days on Hold' to remove the hashtag as that is a custom field, and I have to request someone else to change that.  However, I made your suggested updates, and am still getting the same results - any thoughts on the below?

aggregator.namekey=Active Weeks
aggregator.valuefield=Active Weeks
displayname=Active Weeks
linkedname=direct
namekey=Active Weeks
querysort=Active Weeks
textmode=true
valueexpression=SUB({DE:Actual Duration Wks}, {DE:# of Days On Hold})
valueformat=doubleAsInt

[Heather_Kulbacki]

I would try getting rid of: namekey, querysort, and linkedname. If you get it working you can add them back in one at at time and see if that affects anything.

I also think your aggregator.valuefield needs to be aggregator.valueexpression, with the same formula as your valueexpression. But I would also get rid of both those lines and add them back in together when you get the formula working.

[JulieHand]

Thank you so much!  I have stripped out a lot of stuff now (appreciate that advice!), but still getting the same results - below is my revised formula:

displayname=Active Weeks
textmode=true
valueexpression=SUB({DE:Actual Duration Wks}, {DE:# of Days On Hold})
valueformat=doubleAsInt

Question - would the 'value format' have anything to do with this?  The Value Format for # of Days on Hold is valueformat=customNumberAsString; the Value format for Actual Duration Wks is valueformat=doubleAsInt

Would that cause an issue as they are different?

[skyehansen]

I'm still struggling on figure out if you're using the correct custom field names. Before you mentioned that you couldn't change one of the fields because it was a custom field... but isn't the other one also a custom field, and didn't you change it? Is it possible to get your system admin involved, and confirm that both the DE's are custom fields, and then similarly confirm what the field names truly are?

In terms of what the valueformat is, I personally start off using HTML for most of my valueexpressions, before playing around with any of the others. But if you are getting ANY result at all, it probably isn't a valueformat issue)

[JulieHand · Accepted solution]

Hi Skye,

Thank you for your time and insight here - you were correct in that 'Actual Duration Wks' is NOT a custom field, it is a calculated column in the report.  This helped me get the results I needed - the Actual Duration Wks is calculated from other data points, so I copied that calculation itself into my formula and got the results I needed.  In case this is every helps someone else (not likely, but you never know!), I have pasted the revised formula below:

displayname=Active Weeks
textmode=true
valueexpression=SUB(DATEDIFF({actualCompletionDate},{actualStartDate})/(7), {DE:# of Days On Hold})
valueformat=doubleAsInt

[J_Mas]

You might want to try this as well. Since the Number of Days on Hold is stored as a string (and not a number), you could try the NUMBER function to convert the value in that field within your formula.

displayname=Active Weeks
textmode=true
valueexpression=SUB({DE:Actual Duration Wks}, NUMBER({DE:# of Days On Hold}))
valueformat=doubleAsInt