Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Thanks everybody
I resolved with
sum( aggr(min(if(GrossMargen>=0.2,PurAgentComs,0)),DocNum,PurAgent)*PurAgentTotal)
Regards,
Fernan Gonzalez
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?
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.
hi,
I need totals by vendor.... avg(grossmargin), sum(sales) and sum(total commission) = sum(sales*mincoms)
Regards,
Fernan
Hi,
I don't seems to understand your point.
But have you try
=Aggr(sum(Coms),GrossMargin,[Vendor ])
Regards,
Gabriel
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
Hi,
try this
=sum([ Sale ])*Min(TOTAL<[Vendor ]>Coms) instead of sum(sales*mincomm)
regards
Gabriel
Hi,
Check the attached file.
Thanks everybody
I resolved with
sum( aggr(min(if(GrossMargen>=0.2,PurAgentComs,0)),DocNum,PurAgent)*PurAgentTotal)
Regards,
Fernan Gonzalez