How i can do this?

[#1]

I have two tables, one content the invoices where i have the vendors, sale and gross margin.

The other table content the comision of vendor depending of grossmargin of sale.

pe. table1

Vendor     Sale          GrossMargin

1              100.00        .25

1              250.00       .15

2              200.00       .2

2              150.00       .28

table 2

Vendor   Coms

1               0.0035

1               0.004

1               0.0065

1.              0.008

2               0.003

2               0.0045

2               0.0075

How i can get the total comision for every vendor depending of gross margin?

i itry this    sum(if(GrossMargin>=0.2,min(vendorcoms),0)*Sale) but the result is null.

Regards

Fernan Gonzalez

[#2]

Hi,

You can not have an aggregate function within another in his example

MIN () inside of SUM ().

If GrossMargim> = 0.2 is the lowest value returned to committee, and if less, what value should be considered?

you could create a table that shows the end result of your application?

[#3]

see attached. I put a chart table together... mincomm is the minimum commission by vendor. Expression in the table...

Min

( Total <Vendor> Commission)

and then you just multiply mincomm times sales.

Hope this helps.

[#4]

hi,

I need totals by vendor.... avg(grossmargin), sum(sales) and sum(total commission) = sum(sales*mincoms)

Regards,

Fernan

[Gabriel]

Hi,

I don't seems to understand your point.

But have you try

=Aggr(sum(Coms),GrossMargin,[Vendor  ])

Regards,

Gabriel

[#6]

Ok sorry.

I need totals by vendor

vendor        grossmargin           sales               comission

x               avg(grossmargin)     sum(sales)     sum(sales*mincoms)

1                      .20                       350.00          350*0.0035

2                      .24                       350.00          350*0.003

But when i use Min( Total <Vendor> Commission)  as mincomm to calculate sum(sales*mincomm) the expression have an error.

Regards,

Fernan

[Gabriel]

Hi,

try this

=sum([   Sale         ])*Min(TOTAL<[Vendor  ]>Coms) instead of sum(sales*mincomm)

regards

Gabriel

[CELAMBARASAN]

Hi,

Check the attached file.

[#9 · Accepted solution]

Thanks everybody

I resolved with

sum( aggr(min(if(GrossMargen>=0.2,PurAgentComs,0)),DocNum,PurAgent)*PurAgentTotal)

Regards,

Fernan Gonzalez