topic Re: Sum Error in Charts in QlikView

Sum Error in Charts

Thu, 12 Jul 2012 04:51:43 GMT

hi,

I have three columns..

A	B	Amount
1000	25	50
32	1000	82
1500	78	90
1300	89	54
25	1300	60

I need to sum up the Amount when Column A = Column B. eg: First 2 rows, 1000 is in both columns but has different adjacent values, so i have to sum up 50 + 82 and similarly Row 4 and 5, since 1300 is in both columns need to sum up, 60 + 54. whereas row 3, is a single unique line item and that remains the same. Plz help me find the right expression.

Re: Sum Error in Charts

Thu, 12 Jul 2012 06:19:38 GMT

A	B	Amount	=
1000	25	50	132
32	1000	82	132
1500	78	90	90
1300	89	54	114
25	1300	60	114

right?

in this case?

A	B	Amount	=
1000	1400	31	96?
1400	78	32	96?
89	1000	33	96?

Re: Sum Error in Charts

Thu, 12 Jul 2012 06:31:59 GMT

Hi,

Can you do an applymap function in the script level between A & B to get the amount correspondingly ,

and call this calculated amount in the expression directly

Regards,

Kiruthiga

Re: Sum Error in Charts

Thu, 12 Jul 2012 06:34:02 GMT

the second case can never happen cause for each unique number in column A, there can be only on corresponding line item in B.

Re: Sum Error in Charts

Thu, 12 Jul 2012 07:23:22 GMT

Hi, Try (Amount)+FieldValue('Amount',FieldIndex('B',sum(A))) Please see file attached. Regards Rahul

Re: Sum Error in Charts

Thu, 12 Jul 2012 07:26:53 GMT

Thanks.. It surely works.. but the first row has a value 110.. Why is that?

Thanks & Regards,

Shilpa

Re: Sum Error in Charts

Thu, 12 Jul 2012 07:37:04 GMT

A 25 =60 B 25 =50 Therefore value equals 60+50 = 110

Re: Sum Error in Charts

Thu, 12 Jul 2012 07:54:31 GMT

"the second case can never happen cause for each unique number in column A, there can be only on corresponding line item in B." is not correct...?

Re: Sum Error in Charts

Thu, 12 Jul 2012 08:54:50 GMT

Hi Rahul,

Your expression is giving me th required output but it is not working in my application.

Re: Sum Error in Charts

Thu, 12 Jul 2012 09:11:45 GMT

Hi Shilpa, If possible, could you post an example? Regards R

Re: Sum Error in Charts

Thu, 12 Jul 2012 09:35:15 GMT

hi.. with the expression you have given, i am getting correct values for those which are in both columns, but for the unique values.. I am not getting values.

And second thing, when i try with 5 line items it works.. but with Larger number of items and different names it doesnt work..

Re: Sum Error in Charts

Thu, 12 Jul 2012 10:05:59 GMT

use the following syntax for amount and it should work: =if(FieldIndex('B',sum(A))=0,Bal, Bal + FieldValue('Bal',FieldIndex('B',sum(A)))) Regards Rahul

Re: Sum Error in Charts

Thu, 12 Jul 2012 10:22:56 GMT

Now it is giving me all the values.. even for the single items.. which should not be the case na... In my QVW, i should have only 3 values..

Re: Sum Error in Charts

Thu, 12 Jul 2012 10:40:46 GMT

=if(FieldIndex('B',sum(A))=0,if(FieldIndex('A',sum(B))=0,Bal), Bal + FieldValue('Bal',FieldIndex('B',sum(A))))

Re: Sum Error in Charts

Thu, 12 Jul 2012 10:49:44 GMT

It is working correctly.. thanks all..

Re: Sum Error in Charts

Thu, 19 Jul 2012 09:02:15 GMT

Name	A	B	Total
ABC	1000	4425	50
ABC	32	1000	82
GHY	1500	78	90
LKJ	1300	4489	54
LKJ	25	1300	60
ABC	1600	56	99

If i add a new field "name" as a dimension for the expression above, values are not matching. Please help.

Re: Sum Error in Charts

Thu, 19 Jul 2012 09:14:08 GMT

Dear Shilpa, How are you? Could you please post an example? Regards R

Re: Sum Error in Charts

Thu, 19 Jul 2012 09:28:08 GMT

Please find the example attached.. the expression should ideally work for all dimensions.. bt my example is not giving values

Re: Sum Error in Charts

Thu, 19 Jul 2012 10:00:09 GMT

Dear Shilpa,

Try the following:

aggr(if(FieldIndex('B',sum(A))=0,if(FieldIndex('A',sum(B))=0,Bal), Bal + FieldValue('Bal',FieldIndex('B',sum(A)))),[Doc. No.])

It should do the trick.

The reason why it was not working is because your Customer Name is not unique, therefore we are aggr this syntax over a unique field now. (Asuming your Doc No is a unique field)

Regards Rahul Lakhina

Re: Sum Error in Charts

Thu, 19 Jul 2012 10:08:54 GMT

thanks it is working.. but one doubt.. if i do not want Doc no and i want any other dimension.. or no other dimension,.. then?