Post

7 followers Follow
1
Avatar

Convert fields (currency -> number)

In a highlight in a report I want to count all the instances where some currency field has the value "0", so I am using "filter related items":

$C_IGA = 0 -> error: Data types Currency and Numeric not compatible for '=' operation

$C_IGA = currency(0) and $C_IGA/Currency(1) = 0 -> error: "Function Currency() can be used only in calculated field"

 

How can I count the number of items where the value of a currency field is "0"?

 

 

Peter Fjelsten Not planned

Please sign in to leave a comment.

14 comments

0
Avatar

Hi Peter,

Please try $C_IGA = NULL. 

I have also added a feature request to add currency() function to the formula in reports, so that you can convert currency fields to numeric, the same way you currently can in our customization engine. ID for your reference: **CR-259958

**All the best,

Diana

Diana Sandura 0 votes
Comment actions Permalink
0
Avatar

Hi Diana,

I do not need the ones with NULL but where the currency value is zero/0

Peter Fjelsten 0 votes
Comment actions Permalink
0
Avatar

Hi Peter,

You can try to create a currency custom field, that is formula based: currency(0). Then compare: 

$C_IGA=$C_ZeroCurency. 

Diana

Diana Sandura 0 votes
Comment actions Permalink
0
Avatar

Is this really the only option? Can it not be done with a formula?

Peter Fjelsten 0 votes
Comment actions Permalink
0
Avatar

Currently, this requires a bit of a workaround with a custom field, but we are planning to add currency() function to reports formula, so that it becomes possible in the near future. 

Diana Sandura 0 votes
Comment actions Permalink
1
Avatar

I ran into another case today where I need this (i.e. to make greater than for a currency field) - so is there any update to this?

Peter Fjelsten 1 vote
Comment actions Permalink
1
Avatar

Hi Guys, 

I still struggle with the topic you started 4 years ago. Have you managed to resolve it?

Maciej

Maciej Skorczewski 1 vote
Comment actions Permalink
0
Avatar

Hello Maciej,

Say one wants to create a report highlight that counts projects with 0 Actual Cost. Steps to implement this:

1) Create a custom currency field on the Project entity with formula equal to Currency(0):

 

2) Create the report and add a highlight:

 

3) Results:

I hope this is helpful.

Best regards,

Roland

Roland Pumputis 0 votes
Comment actions Permalink
0
Avatar

Is there any update on CR-259958? I need to be able to add a calculated field that converts a currency field and multiplies it by another numeric field within a report. To do this I need to convert the currency field to numeric but the Currency() function is not available in reporting

Tom Mackean 0 votes
Comment actions Permalink
0
Avatar

You can create a custom field (formulaic) to do the conversion and then use the custom field in the report.

Josh Santos 0 votes
Comment actions Permalink
0
Avatar

Hi Thomas,

I think you should be able to multiply a currency field by a numeric field - no conversion should be needed.

Could you please check and confirm?

Thank you,

Roland

Roland Pumputis 0 votes
Comment actions Permalink
0
Avatar

Hi Roland - I can do this but this will only multiply by the currency amount that is the system currency (in my case USD). What I want to do is multiply by the local currency amount which is a non-USD based currency. Do you know a way to do this?

Tom Mackean 0 votes
Comment actions Permalink
0
Avatar

Hi Thomas,

In that case, you'll have to create a custom field as per Josh's recommendation. As for the CR-259958, there's still not ETA.

I hope this helps.

Roland

Roland Pumputis 0 votes
Comment actions Permalink