topic Re: sum set analysis in QlikView

sum set analysis

Thu, 06 Mar 2014 23:52:28 GMT

Can someone help me on this question?

I have a table contain quite a few fields like below. Lets say two of fields are A and B. I want to calcuate sum(A*B). The question is that I expect sum(A*B) equals to 10+2+18. but it is not.

table:

---------------------------

key, A, B, A*B...

1 , 2, 5, 10,...

2 , 1, 2, 2,...

3 , 6, 3, 18,...

---------------------------

if I select key field 1, the table become like below, just have the first row.

------------------------------

key, A, B, A*B...

1 , 2, 5, 10,...

------------------------------

I expected sum(A*B) = 10. But its calcuated result is (2+1+6)*5=45.

if I did sum(B)*A it will give me 5*2=10

I am confused. Under the current selection, A should just be number 2, why when I did sum(A*B), it sum all A value (2, 1, 6)?

Thanks,

Re: sum set analysis

Anonymous — Fri, 07 Mar 2014 00:07:38 GMT

if you want to use corresponding rows for A and B to be multiplied for table, use order by key while loading Table.. other wise it will not know which A value to multiply with B and finally make sum.. and qvd can not support orderby only resident table

Re: sum set analysis

rwunderlich — Fri, 07 Mar 2014 01:05:21 GMT

I can't explain from the information you've given. Can you post a qvw that shows the problem?

-Rob

Re: sum set analysis

Fri, 07 Mar 2014 08:22:56 GMT

I cant replicate this..

If I put the data into an INLINE table

Load * INLINE [

key,A,B

1,2,5

2,1,2

3,6,3

];

Then load the script and create a chart object straight table using Dimension as Key - no matter what I do - SUM(A*B) always give correct answer.

As Rob says - can you attached a qvw ?

Re: sum set analysis

ashwanin — Fri, 07 Mar 2014 08:43:39 GMT

Hi Chayong,

QV is reflecting the same, what you required in your output.

have a look in attached QV.

Re: sum set analysis

er_mohit — Fri, 07 Mar 2014 09:10:57 GMT

see the attached file

Re: sum set analysis

Fri, 07 Mar 2014 09:16:41 GMT

Hi, everyone, thanks for helping, I think one thing I did not make it clear. It was that column B is 1-1 relationship with key, but A column is joined in table by another key value C. This may cause the problem. But still not sure how to solve

Re: sum set analysis

Mon, 10 Mar 2014 15:23:06 GMT

Hi, Thanks a lot for helping. Here is the example of my question.

Re: sum set analysis

Mon, 10 Mar 2014 15:24:33 GMT

I find one solution.

//Rate1:

//Load distinct

//System,RatePermm

//resident Brokerage;

Re: sum set analysis

Mon, 10 Mar 2014 15:26:11 GMT

Hi, Here is the example of my problem and now I find one solution for it.

//Rate1:

//Load distinct

//System,RatePermm

//resident Brokerage;