How to convert a string to number?

Zac_Tevin — Mon, 23 May 2022 16:01:42 GMT

Hi,

I want to use set analysis to count the Number of Distinct Application Numbers where the financial year is lower than the selected "financial year" in the selection filter.

I have used the below formula:

Count(Distinct{<[Financial Year]={"<$(=Num(Subfield(GetFieldSelections([Financial Year]),', ', 1)))"}>}[Application Number]). However, this formula returns 0 as it isn't working as it should.

Trying to pinpoint the issue, Num(Subfield(GetFieldSelections([Financial Year]),', ', 1)) returns '-'

Subfield(GetFieldSelections([Financial Year]),', ', 1)) returns the selection 2015-2016, so I figured it's an issue with the num function.

How can I get the formula to work, by converting the string to a number? and count the Number of Distinct Application Numbers where the financial year is lower than the selected "financial year" in the selection filter.

Could anyone please help.

Thanks in Advance.

Re: How to convert a string to number?

edwin — Mon, 23 May 2022 18:30:00 GMT

your problem is that Financial Year is a string. there are i suspect a number of ways to go about this.

simplest is if your data will always be Year - Year and that it will always be consecutive: 2014-2015, 2015-2016, 2016-2017, etc. you dont need NUM function, 2014-2015 string will be less than 2015-2016 string

but to make sure your logic is scalable, create another field that you can use for your calculations like Year, where fiscal year 2014-2015 is considered Year 2014. you dont have to show Year to your users all they see is Fiscal year so in your expression

{<[Fiscal Year]=, Year={"<$(=subfield(concat(Year,','),',',1))"}>}

this gets all the Fiscal years with year < the first selected year

topic Re: How to convert a string to number? in App Development

How to convert a string to number?

Re: How to convert a string to number?