Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
jjlyngle
New Contributor II

expression when field value is equal to [<90]

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?

9 Replies
Partner
Partner

Re: expression when field value is equal to [<90]

Try:

Field = { " < 90 " }

rajeshvaswani77
Valued Contributor III

Re: expression when field value is equal to [<90]


Hi,

You can try if(field<90, true condition, false condition)

thanks,

Rajesh Vaswani

jjlyngle
New Contributor II

Re: expression when field value is equal to [<90]

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%')

Partner
Partner

Re: Re: expression when field value is equal to [<90]

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%')

Partner
Partner

Re: expression when field value is equal to [<90]

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.

jjlyngle
New Contributor II

Re: expression when field value is equal to [<90]

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%')


Partner
Partner

Re: expression when field value is equal to [<90]

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

Partner
Partner

Re: expression when field value is equal to [<90]

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%')

jjlyngle
New Contributor II

Re: expression when field value is equal to [<90]

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.