topic Re: Problem in Calculation in QlikView

Problem in Calculation

Wed, 26 Jun 2013 07:57:40 GMT

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

Re: Problem in Calculation

Anonymous — Wed, 26 Jun 2013 08:09:46 GMT

Does this expression not work:

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

Jonathan

Re: Problem in Calculation

Anonymous — Wed, 26 Jun 2013 09:45:10 GMT

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

Re: Problem in Calculation

Gysbert_Wassenaar — Wed, 26 Jun 2013 10:07:23 GMT

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

Re: Problem in Calculation

Wed, 26 Jun 2013 10:13:35 GMT

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

Re: Problem in Calculation

flipside — Wed, 26 Jun 2013 10:20:03 GMT

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)

Re: Problem in Calculation

Wed, 26 Jun 2013 10:35:55 GMT

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

Re: Problem in Calculation

flipside — Wed, 26 Jun 2013 10:52:49 GMT

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 !

Re: Problem in Calculation

Gysbert_Wassenaar — Wed, 26 Jun 2013 11:07:42 GMT

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

Re: Problem in Calculation

Wed, 26 Jun 2013 11:29:09 GMT

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

Re: Problem in Calculation

Thu, 27 Jun 2013 07:55:14 GMT

hiii sir, flipside

you are right regarding to selection in list box the data comes all rather than to show selected value for resolving this problem i have done in calculated dimension like this

aggr(only({<MAT_CODE -=p({<MOV_TYPE={0}>})>}MAT_CODE),MAT_CODE)

and same expression as told by sir Gysbert Wassenaar

Regards

vishwaranjan