topic Re: Problem with Sum aggregate Function in Chart type Pivot table in QlikView

Problem with Sum aggregate Function in Chart type Pivot table

Thu, 29 Sep 2011 14:13:16 GMT

Dear All,

When I am using sum aggregate function in chart type Pivot table in expressions then .

It is showing double value without this function it is showing actual value.

It is showing allactual no. of rows without this function it is not showing some rows.

Please Help.

Thanks In Advance.

Re: Problem with Sum aggregate Function in Chart type Pivot table

IAMDV — Thu, 29 Sep 2011 14:30:56 GMT

1. It is not showing double values. It is performing aggregation on the No_ field.

For example:

Field No_ : EX/PUN/11-0190 has two values for Quantity. And both the values are 1800. Same is the case for all the No_ numbers. I have added one more column with Count(No_) to prove this is right.

2. Qlikview always shows the distinct values and that's how QV engine works. So without using the aggregtion function - SUM/COUNT, you will only see the distinct values. However, if the values are different.. for example Field No_ : EX/PUN/11-0194 which contains 6400 and 60000 as Quantity. So QV can't decide which value to show and it will exclude this Row until you use the aggregation function.

I am attaching the updated QV document for your understanding.

I hope this makes sense. Let me know if you have any questions.

Cheers - DV

Re: Problem with Sum aggregate Function in Chart type Pivot table

sridhar240784 — Thu, 29 Sep 2011 14:35:41 GMT

Hi Anil,

It is always advicable to use Aggrgegate function in QlikView. There are two scenarios here.

1) It is showing double value without this function it is showing actual value.

- Yes you are correct.Without aggregation, Where ever QV see the more that one same value for 'No_', it wil show only one value. For example 'EX/PUN/11-0190 2' has Quantity of 1800 two times but it show only one time.

2) Some rows are missing

- Lets take an example, for 'No_' --- 'EX/PUN/11-0194 4' has Quantity of 6400 and 6000 . In this case 'EX/PUN/11-0194 4' has two diffrenet Quantity Qlikview is skipping these value without aggregation.

Look at the attached image for more details.

Hope i`m clear.

-Sridhar

Re: Problem with Sum aggregate Function in Chart type Pivot table

its_anandrjs — Thu, 29 Sep 2011 14:36:49 GMT

Hi,

When ever you Sum/Count the values it will need to be used with Sum/Count because there are dupllicate values and in dimension there is relation of this values so how qlikview identifies it. Basically what happen when ever you load data in qlikview if there is two same name table so qlikview idetifies it as a single table or column.

So it is necesaary to use Aggregation funcation to get correct values.

Rgds

Anand

Problem with Sum aggregate Function in Chart type Pivot table

SunilChauhan — Thu, 29 Sep 2011 14:40:18 GMT

sum(distinct [Line Amount])

use the code in expression

hope this give you direction

Re: Problem with Sum aggregate Function in Chart type Pivot table

Thu, 29 Sep 2011 14:50:33 GMT

Hi, DV

First of all thanks for quick reply. I add a table box in your QV I am not able to see the double value or IInd row. please explain how can i see double row.

Thanks & Regards.

anilsknp

Re: Problem with Sum aggregate Function in Chart type Pivot table

its_anandrjs — Thu, 29 Sep 2011 14:55:11 GMT

Hi,

For this use pivot table in that you are able to see more than rows.

Rgds

Anand

Re: Problem with Sum aggregate Function in Chart type Pivot table

Thu, 29 Sep 2011 14:55:14 GMT

Hi, Sridhar

First thanks for very quick reply.

Please tell me that how can I see the double row of field [No_]. I add a table box in my QV it is not showing the double row.

Thanks & Regards.

Anil

Re: Problem with Sum aggregate Function in Chart type Pivot table

sridhar240784 — Thu, 29 Sep 2011 14:58:02 GMT

Anil, If you want to see a double value without using the aggregation function. Creata dummy field in Back end (i.e. Edit Script) like Rowno() as RowNum and use this field in front end and use table box to view the data.

Hope this helps you.

-Sridhar

Re: Problem with Sum aggregate Function in Chart type Pivot table

IAMDV — Thu, 29 Sep 2011 14:59:15 GMT

Hi Anilsknp,

I am attaching the updated QV document with TableBox. You can see the Double Values. Remember you will only see the double values only once. As mentioned in my previous post, QV always shows the repeated values once.

Cheers - DV

Re: Problem with Sum aggregate Function in Chart type Pivot table

IAMDV — Thu, 29 Sep 2011 15:08:18 GMT

Hi Anil,

Just adding some more details on how it works within QV Engine.

Each different data element value of each data element type is assigned a binary code and the data records are stored in binary-coded form. By using the binary coding, QV searches very rapdly on the tables. Morever, redundant information can be removed, resulting in a reduced amount of data. When the user has selects any data element, the binary-coded data records are examined in order to find all data element values implied by the selection.

I hope now you understand the concept of having distinct values.

Also, as mentioned by Sridhar...please use the RowNo() function in the load script to see all the values. By adding the RowNo() function in the load script QV creates a unique record and you can use the same RowNo() field in the Table Box.

Cheers- DV

Re: Problem with Sum aggregate Function in Chart type Pivot table

Thu, 29 Sep 2011 15:38:43 GMT

hi D V,

again thanks for reply. yes you right it is showing every row double. it means problem in my data loading script / SQL View.

Thanks & Regards.

Anil

Re: Problem with Sum aggregate Function in Chart type Pivot table

Thu, 29 Sep 2011 15:42:06 GMT

Hi, Sridhar

Thanks for your Quick reply.

Regards

Anil

Re: Problem with Sum aggregate Function in Chart type Pivot table

Thu, 29 Sep 2011 15:46:25 GMT

Thanks to all, who's reply me very fast

DV, Sridhar, Anand, Sunil Chauhan

Again Thanks

with Regards.

Anil