How to convert a string to number?

[Zac_Tevin]

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.

[edwin]

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