how to calculate sales volume in the province

[limingthefirst]

Hi,

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

D@province	C@province	Qty
A	A	100
A	B	200
A	C	300
B	B	400
B	D	500
B	C	600

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!

[swuehl · Accepted solution]

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.

[limingthefirst]

Thank you very much!

I get what I want!

[raman_rastogi]

Sorry your question is not clear to me

try this

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

[anderseriksson]

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(...)

[PradeepReddy]

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

Script:

Load *,

      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...

[limingthefirst]

thank you!

You gave me some inspiration for other works