Subtraction on Custom Fields in Report

Question

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

JulieHand · Accepted Answer

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)

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

skyehansen · Answer

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.

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded