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

[mikevwang]

I'm pretty sure this works:

=Aggr(Only(If([Net Sales Order US Dollar Amount Yotta]/[Net Sales Order US Dollar Amount CDR]<=0.97 or [Net Sales Order US Dollar Amount Yotta]/[Net Sales Order US Dollar Amount CDR]>=1.03, [Business Area Code])), [Business Area Code]) 

[mikevwang]

I think this is what you are looking for if you don't want to reproduce the original listbox.

=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]
)

[soniasweety]

Hi Tresesco  any solution?

[soniasweety]

the same expression i want to show in text box ...with the count ?
how can i modify this ?Mikevwang

[soniasweety]

thank you  Mikevwang   list box it is working but in text box its showing wrong count.

the same expresision how can i modify in text object to get the listbox count?

[mikevwang]

='# of Match rate Red Count:'& chr(13) &sum( aggr( IF(SUM([Net Sales Order US Dollar Amount Yotta])/SUM([Net Sales Order US Dollar Amount CDR])>0.97 and
SUM([Net Sales Order US Dollar Amount Yotta])/SUM([Net Sales Order US Dollar Amount CDR])<1.03,0,1),[Business Area Code],[Fiscal Year Month Code],[Profit Center Level 0 Name]))

[soniasweety]

how to  put  comments like and helpful ? like earlier community wise ?

[soniasweety]

Hi,

Franky_h79 

Stalwar1

Mikevwang 

i want to show the  codes where the match rate  is null that  codes  should show in the listbox  how can i modify the expression in listbox?  

include  -   SUM([Net Sales Order US Dollar Amount Yotta])/SUM([Net Sales Order US Dollar Amount CDR])  =null or 0

=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]

[soniasweety]

Hi Tresesco     how can i include if match rate is null i want to show that codes in the listbox 

[tresesco]

Sorry, I could not follow your requirement.