topic How i can do this? in QlikView

How i can do this?

Thu, 14 Feb 2013 17:25:56 GMT

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

Re: How i can do this?

Thu, 14 Feb 2013 19:36:49 GMT

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?

Re: How i can do this?

Thu, 14 Feb 2013 20:01:07 GMT

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.

Re: How i can do this?

Thu, 14 Feb 2013 20:16:28 GMT

hi,

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

Regards,

Fernan

Re: How i can do this?

Gabriel — Thu, 14 Feb 2013 21:09:18 GMT

Hi,

I don't seems to understand your point.

But have you try

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

Regards,

Gabriel

Re: How i can do this?

Thu, 14 Feb 2013 21:30:34 GMT

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

Re: How i can do this?

Gabriel — Thu, 14 Feb 2013 21:42:23 GMT

Hi,

try this

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

regards

Gabriel

Re: How i can do this?

CELAMBARASAN — Thu, 14 Feb 2013 21:52:08 GMT

Hi,

Check the attached file.

Re: How i can do this?

Fri, 15 Feb 2013 14:26:19 GMT

Thanks everybody

I resolved with

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

Regards,

Fernan Gonzalez