Replace formulae

[soniasweety]

Hi all,  @ Franky_h79    Andrey_krylov 

Sunny Talwar 

i am using

below  expression its working fine  in listbox.  where as   rate filed is coming from database directly.

=Aggr(Only({<[Rate] = {"<=0.97>=1.03"}>} [ Code]), [ Code])          [listbox expression]

but now the issue is    rate column is not coming from db . so we need to caluclate on the fly. 

so i used this  in the script to caluclate rate

Num([ AmountB]/[ AmountA], '#,##0.00%') as [Rate],

so now   my requirment is ...  i want to replace the  rate with   above expression  to be used in listbox.

=Aggr(Only({<[Rate] = {"<=0.97>=1.03"}>} [ Code]), [ Code])      -- here how can place  rate exression instead of direct rate

thanks 

sony

[soniasweety]

i have list box  now  i m showing the below expression working fine which gives me  the codes which is <97% and >103%

 Tresesco 

=Aggr(
If(
SUM([Net Sales Order US Dollar Amount Yotta])/SUM([Net Sales Order US Dollar Amount CDR])<=0.97
or SUM([Net Sales Order US Dollar Amount Yotta])/SUM([Net Sales Order US Dollar Amount CDR])>=1.03 ,
[Business Area Code]
)
,[Business Area Code],[Fiscal Year Month Code],[Profit Center Level 0 Name])

but  my user  added one more logic : that is   if     SUM([Net Sales Order US Dollar Amount Yotta])/SUM([Net Sales Order US Dollar Amount CDR])=0 or null   codes  i want to show that also in listbox  it is not showing in the listbox..   i want to include this logic also inside that

[mikevwang]

You can wrap SUM([Net Sales Order US Dollar Amount Yotta])/SUM([Net Sales Order US Dollar Amount CDR]) in ALT(.... , 0) like ALT(SUM([Net Sales Order US Dollar Amount Yotta])/SUM([Net Sales Order US Dollar Amount CDR]),0)<=0.97. That turns nulls to 0, but 0 should already work when compared vs <=0.97.

[soniasweety]

i added this 

working fine... is this correct way?

=Aggr(
If(IsNull(SUM([Net Sales Order US Dollar Amount Yotta])/SUM([Net Sales Order US Dollar Amount CDR])) or
SUM([Net Sales Order US Dollar Amount Yotta])/SUM([Net Sales Order US Dollar Amount CDR])<=0.97
or SUM([Net Sales Order US Dollar Amount Yotta])/SUM([Net Sales Order US Dollar Amount CDR])>=1.03 ,
[Business Area Code]
)
,[Business Area Code],[Fiscal Year Month Code],[Profit Center Level 0 Name])

[mikevwang]

yes that should work too

[soniasweety]

thanks. i will validate with live app and come back to u. thanks for your quick response