topic Re: Summary field false total in QlikView

Summary field false total

Wed, 28 Dec 2011 15:04:03 GMT

Hello Everyone,

I'm new to QV and have some questions, becouse I found a problem within my report.

I'm trying to calculate SUM on field Punktacja_Punkty from Table Punktacja. This table has the same ID field IDNagroda as the field in Table Kotly in which I gather the data (some serial numbers, users name and productID).

Table Kotly	Table Punktacja	Table Users
User_ID	IDNagrody	User_ID
IDNagrody	Punktacja_Nagr	Users_UserName
Kotly_ID	Punktacja_Ean	Users_Passw
Kotly_Kod	Punktacja_Punkty
Kotly_DataZa
Kotly_Rok
Kotly_Dystr

The data from red fields and green are relational. Everything works during the data lookup ok but I wonder why SUM(Punktacja_Punkty) expression is not creating total of the filtered content. I.E. the sum for data below is only 26 instead of 226. Infact count(Kotly_ID) = 42 as it should.

Kotly_ID	Kotly_Kod	Punktacja_Punkty	Users_UserName
3556	1	8	username
3555	2	8	username
3557	3	8	username
3552	4	8	username
3553	5	8	username
3554	6	8	username
3559	7	8	username
3558	8	8	username
3550	9	3	username
3547	10	3	username
3549	11	3	username
3548	12	3	username
3551	13	3	username
3452	14	3	username
3448	15	3	username
3447	16	3	username
3457	17	3	username
3456	18	3	username
3453	19	3	username
3451	20	3	username
3449	21	3	username
3455	22	3	username
3454	23	3	username
3450	24	3	username
3458	25	3	username
3437	26	3	username
3436	27	3	username
3439	28	3	username
3441	29	3	username
3443	30	3	username
3445	31	3	username
3442	32	3	username
3440	33	3	username
3435	34	3	username
3438	35	3	username
3444	36	3	username
3446	37	3	username
3564	38	15	username
3562	39	15	username
3560	40	15	username
3561	41	15	username
3563	42	15	username

Can you gice me a feedback how to summarize corectly and how to add the sum field i.e in TableBox Object or MultiBox Obj?

Best regards and sorry if it's to simple...

Marcin

Summary field false total

pover — Wed, 28 Dec 2011 18:48:50 GMT

Marcin,

Can you post your QV or at least an image of what you're seeing?

Karl

Re: Summary field false total

Thu, 29 Dec 2011 08:17:04 GMT

Hi Karl,

thanks gor replay. As attachements you will find 3 files - textBox with properities, same as TableBox and tables schema. Below I paste the script tables.

Best regards and thx for answering.

SQL SELECT Id as Kotly_ID,

kod as Kotly_Kod,

typkotla as IDNagrody,

dataZakupu as Kotly_DataZakupu,

rok as Kotly_Rok,

DataWpisu as Kotly_DataWpisu,

dystrybutor as Kotly_Dystrybutor,

userId as User_ID

FROM db168409.Kotly;

SQL SELECT ID as IDNagroda,

Nazwa as IDNagrody,

Opis as Nagrody_Opis,

Foto as Nagrody_Foto,

Punkty as Nagrody_Punkty

FROM db168409.Nagrody;

SQL SELECT ID as NagrodyZreal_ID,

IDNagrody as IDNagrody,

Rozmiar as NagrodyZreal_Rozmiar,

Punkty as NagrodyZreal_Punkty,

IDUzytkownik as NagrodyZreal_IDUzytk,

Data as NagrodyZreal_Data

FROM db168409.NagrodyZreal;

SQL SELECT ID as IDNagrody,

NazwaKotla as Punktacja_NazwaKotla,

KodEanKotla as Punktacja_EanKotla,

Punkty as Punktacja_Punkty

FROM db168409.Punktacja;

SQL SELECT ID as User_ID,

usern as Users_UserName,

passwd as Users_Passwd,

role as Users_Role

FROM db168409.Users;

SQL SELECT uemail as User_ID,

imie as UsersFull_Imie,

nazwisko as UsersFull_Nazwisko,

sapno as UsersFull_SapNo,

company as UsersFull_Company,

kodpoczt as UsersFull_KodPoczt,

miasto as UsersFull_Miasto,

adres as UsersFull_Adres,

telefon as UsersFull_Telefon

FROM db168409.UsersFull;

Summary field false total

pover — Thu, 29 Dec 2011 16:45:13 GMT

Hi Marcin,

The problem is that the table Punktacja is a reference table like a price list or grade table and doing a sum just on that table will sum up the amounts without respecting how many times it is referred to in the the table Kotly. You should either do a join between the Kotly and Punktacja table in the script or add a quantity column in the Kotly table to get the sum you are looking for.

Out of curiosity Kotly is drum and Punktacja is a grade scale? I'm just wondering how good the polish-english translators are.

Karl

Re: Summary field false total

Thu, 29 Dec 2011 20:13:49 GMT

Hi Karl,

yes the Punktacja is a grade scale, but Kotly in this context are boilers (gas boilers I was thinking of).

afterall I wanted to summarize by the points of Kotly for each of users and also get the participation (count) the boilers by type.

Infact I think I'll try to create one more dataset for this purpose. Do you think its better to join the tables or concatenate them??

Marcin

Summary field false total

pover — Thu, 29 Dec 2011 22:43:58 GMT

Marcin, Do a left join between Kotly and Punktacja as you will be adding a column to existing data and not adding rows which would be the case in which you would do a concatenate.

Karl