Problem with Sum aggregate Function in Chart type Pivot table

[#1]

Dear All,

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

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

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

Please  Help.

Thanks In Advance.

[IAMDV]

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

[sridhar240784]

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

[its_anandrjs]

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

[SunilChauhan]

sum(distinct [Line Amount])

use the code in expression

hope this give you direction

[#6]

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

[its_anandrjs]

Hi,

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

Rgds

Anand

[#8]

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

[sridhar240784]

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

[IAMDV]

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