Problem in Calculation

[#1]

hi m stuck off facing the problem but can't get success

please help me.

see the data of my table like this

MAT_CODE	MOV_TYPE	SALES
A	0	100
A	1	200
A	2	300
A	5	400
B	8	500
B	9	600
C	0	700
C	5	800
C	3	900
D	7	1000
D	10	1100
D	2	1200

Now i want the output like this

MAT_CODE	SALES
B	1100
D	3300

here i clarify to you that i want those MAT_COD that don't have MOV_TYPE  equal to 0  and sum total of that SALES with MAT_COD

any help will be appreciated

[Anonymous]

Does this expression not work:

    sum({<MOV_TYPE={'>0'}>} SALES)

Jonathan

[Anonymous]

I just reread your question.

I am not sure how to exclude MAT_CODEs that have a MOV_TYPE of 0 using set analysis, but using if statements:

     sum(if(aggr(count(if(MOV_TYPE='0',MAT_CODE)),MAT_CODE)=0, SALES))

This gets close, but only sums up the first found SALES value (see attached).

Jonathan

[Gysbert_Wassenaar · Accepted solution]

Try: sum({<MAT_CODE=e({<MOV_TYPE={0}>}MAT_CODE)>}SALES)

[#5]

please help .

i tried lots of expression but doesn't get the correct output.

i want those MAT_CODE which have no any 0(ZERO)  MOV_TYPE.

i am working on qv personel edition, so can't open file

Thanks

[flipside]

Hi,

If your MOV_TYPE code zero is the minimum value, then you could use this ...

=sum(SALES) * IF(AGGR(MIN(MOV_TYPE), MAT_CODE)>0,1)

flipside

EDIT: Actually, this is probably better as you can specify any code or list of codes to ignore.

=sum(SALES) * IF(AGGR(COUNT({<MOV_TYPE={0}>} MOV_TYPE), MAT_CODE)>0,0,1)

[#7]

Thanx  sir Gysbert Wassenaar

I Appreciate for your answer its awsome i didn't think about exclude function to use in expression in a simple way

Regards

Vishwaranjan

[flipside]

Unfortunately I'm not sure this works. If you add a list box for MAT_CODE and select just MAT_CODE A, then your results table shows figures for B, C & D.

Maybe Gysbert can tweak the formula to display correctly - I avoid Set Analysis as much as possible !

[Gysbert_Wassenaar]

Try: sum({<MAT_CODE*=e({<MOV_TYPE={0}>}MAT_CODE)>}SALES)

[#10]

sir

if my MOV_TYPE  is greater than zero then whats the probably solution for that.

firstly i use this code

sum({1<MAT_CODE=p({$<MOV_TYPE -={0}>}MAT_CODE)>}SALES)

for selection purpose and m not sure if it works properly but Gysbert Wassenaar

give me right solution for that