How to calculate demensions depending on other sales-type.

[#1]

How to calculate demensions depending on other sales-type.

There 2 type of sales - basic and comission
(IF(sp279=' 1', 'Sale','commission ') AS 'SalesType',)

Sales
LOAD iddoc as idjournal,
 sp279 as Shop
 (IF(sp279=' 1', 'Sale','commission ') AS 'SalesType',)
 sp277 as IdCustomer;
 SQL SELECT *
FROM `DH294`;

If the salestype is 'Sale'- then we calculate Margin as
Margin=Sum(SalesPrice-AccountingsPrice*Amonut)

else if SalesType is Commission (10% from SalesPrice)- then we should calculate Margin as
Margin=Sum(SalesPrice*0,1)

so, as I understand the best way is to add code to expressions or variables.
so.. the question is
how to write the right code?
((IF(sp279=' 1', '=Sum(SalesPrice-AccountingsPrice*Amonut)','=Sum(SalesPrice*0,1)') doesn't work)
and how to do it through Variables, or may be you know the better way..

[fernandotoledo]

In this case you use SUM(IF(TYPE='1', VALOR*PERC1, VALOR*PERC2))

[#3]

?

[johnw]

I believe he was indicating the general form of a solution, sum(if(condition,calc1,calc2)), rather than giving you the EXACT expression you need, which might be this, but it's hard to know without knowing where you're trying to do this calculation (script? chart? dimension? expression?) and what the relevant portions of your data model are.

sum(if(SalesType='Sale',SalesPrice-AccountingsPrice*Amount,SalesPrice*0,1))

[#5]

thanks,

It would be better if I can calculate it in script, but I tried to do it expressions.

What is relewant portion of data model?

[johnw · Accepted solution]

Perhaps something like this:

LOAD
iddoc as IdJournal
,sp279 as Shop
,if(sp279=' 1','Sale','Commission') as SalesType
,if(sp279=' 1',SalesPrice-AccountingsPrice*Amount,SalesPrice*0.1) as Margin
,sp277 as IdCustomer
,fielda as SalesPrice
,fieldb as AccountingsPrice
,fieldc as Amount
...
;
SQL
...
;

And then only do the SUM portion in the expression:

sum(Margin)

The relevant portion of your data model would be which table(s) contain these fields. Above, I've assumed that they're all on the same table. This may not be correct. If they are spread across two or more tables, the script would be different, or I might suggest a completely different approach.