Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, I am trying to write an expression to sum accounts that have a value of "<90" in a specific field. Using the following does not work: field = {'<90'}. Have also tried {'chr(62)90'}. Any suggestions?
Try:
Field = { " < 90 " }
Hi,
You can try if(field<90, true condition, false condition)
thanks,
Rajesh Vaswani
Thank you Carlos, but the returned value s/b 3% and with the {"<90"} it is 310,271,343,480%
Here is what I have:
sum
({<Metrics_CC = {'1'}
,.as_of_dt = {'$(vMaxDate)'}
,_Bucket )
= {"<90"}
>} Total CC)
,'#,##0%')
i see an additional bracket in your expression..
_Bucket )= {"<90"}
try this one..
=num(sum({<[Metrics_CC] = {'1'},[.as_of_dt] = {'$(vMaxDate)'},[_Bucket] = {"<90"}>} [Total CC]),'#,##0%')
Mmmm... I think you have a problem in your Set Analysis. Assuming there is a field called " .as_of_dt ", then you have an error in " ,_Bucket ) ", you have to define or ignore the field Bucket, but it is incorrect to place the parenthesis " ) " just after the field name. Maybe try with :
sum ({<Metrics_CC = {'1'},.as_of_dt = {'$(vMaxDate)'},_Bucket= {"<90"}>} Total CC)
If this works, then you can format the number using the num function, you cannot use sum to do that.
Try:
num ( sum ({<Metrics_CC = {'1'},.as_of_dt = {'$(vMaxDate)'},_Bucket= {"<90"}>} Total CC), '#,##0%' )
Hope this helps.
Sorry, my copy was not valid. here is the actual.
='CTL' &
num(
sum({<ca_credit.Metrics_BDS_CC = {'1'}
,ca_credit.as_of_dt = {'$(vMaxDate)'}
,[LTC_Bucket] = {">90"}
>}ca_credit.TotalCrossComm)
/
sum( {< ca_credit_fclty_1_prt_p.Metrics_BDS_CC = {'1'}
,ca_credit.as_of_dt = {$(vMaxDate)}
>}
ca_credit.TotalCrossComm)
,'#,##0%')
The expression seems correct, just make sure that both sums return the correct number.
Check that vMaxDate is equally used in both Set Analysis formulas, in one field you use single quotes and in the other don't, and... this is a big if, but weren't you looking for values < 90 ?, in your expression you're looking for values > 90.
Regards
i dont see any issue with it except your second part of the expression date is not in quotes..
what is the out put of "vMaxDate variable? may be a sample app will be helpful..
='CTL' &num(sum({<ca_credit.Metrics_BDS_CC = {'1'},ca_credit.as_of_dt = {'$(vMaxDate)'},[LTC_Bucket] = {">90"}>}ca_credit.TotalCrossComm)/
sum( {< ca_credit_fclty_1_prt_p.Metrics_BDS_CC = {'1'},ca_credit.as_of_dt = {'$(vMaxDate)'}>}ca_credit.TotalCrossComm)
,'#,##0%')
Thank you for all of the suggestions. I ended up having the field changed to " >90". In otherwords added a space before the > sign. This was a text field ">90" rather than a search of a numeric field.