Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all, @ Franky_h79 Andrey_krylov
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
i have list box now i m showing the below expression working fine which gives me the codes which is <97% and >103%
=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
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.
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])
yes that should work too