6 Replies Latest reply: Oct 27, 2015 3:17 AM by raman RASTOGI

# how to calculate sales volume in the province

Hi,

I'm trying to calculate the sales volume in the province.the data sources example in the following

D@provinceC@provinceQty
AA

100

AB200
AC300
BB400
BD500
BC600

now, I want the result is

sum(D@province=A)=100  where C@province=D@province

sum(D@province=B)=400  where C@province=D@province

how to write the expression?

=sum({<   >}Qty)

THANKS ALL!

• ###### Re: how to calculate sales volume in the province

I don't really get what you want, but something like

=Sum( If( [D@province]= [C@province], Qty))

Should return 100 resp. 400 for those two lines where both province values match.

• ###### Re: how to calculate sales volume in the province

Thank you very much!

I get what I want!

• ###### Re: how to calculate sales volume in the province

That is actually not a very good solution. The if-funktion is bad for performance.

In the question was actually suggested to use set analysis and that is much better.

But this only works for specific values of D@province like;

=Sum({D@province={"A"}, C@province={"A"} Qty)

A more generic solution for every instance where D@province equals C@province
can be made using the fact that the comparison operator returns 0 or -1 ;

=Sum( -(C@province = D@province) * Qty )

This avoids the slow if-function, only you must negate the result of the comparison since that is -1 when they are equal.

If provinces are not equal the comparison will return zero and so will the Sum(...)

• ###### Re: how to calculate sales volume in the province

Sorry your question is not clear to me

try this

Sum({<D@province={"=C@province"}>}Qty)

• ###### Re: how to calculate sales volume in the province

1) If both fields C@province and D@province are in Same table, create the flag in the script itself..

Script:

Qty,

C@province,

D@province,

IF(C@province=D@province,1,0) as Flag

From Excel;

Expression.. sum({<Flag={'1'}>}Qty)

2) If both fields are in different tables, follow the swuehl approach...

• ###### Re: how to calculate sales volume in the province

thank you!

You gave me some inspiration for other works