topic sum distinct in a expression in QlikView

sum distinct in a expression

Wed, 13 Jan 2010 21:24:04 GMT

i load this table as a crosstable, where 01/2010, 02/2010 and 03/2010 are DATE and the other columns are qualifiers.

CONV	SIGNAL	UNIT	01/2010	02/2010	03-2010
14139	CANAL (A)	P	0,2358	0,2648	0,6345
14139	CANAL (A)	Q	50.309	50.309	50.309
12145	EL GOURMET	P	0,0414	0,0414	0,0414
12145	EL GOURMET	Q	50.309	50.309	50.309
12145	FILM & ARTS	P	0,0414	0,0414	0,0414
12145	FILM & ARTS	Q	50.146	50.146	50.146
14137	CANAL (A)	P	0,6548	0,6486	0,6485
14137	CANAL (A)	Q	64.314	64.314	64.314

P = price and Q = quantity.

i need to calculate P * Q for each CONV and it works when i expand the table at QV; but if i want to see only by SIGNAL Qlikview sums 1° P then sums Q and al least multiply both values.

e.g. for SIGNAL = CANAL (A) in January ....if i use this formula:

=SUM(P) * SUM(Q) = (0.2358 + 0.6548) * (50309 + 64314) = 102083

and it´s good but it´s not what i need.

I need this: (0.2358 * 50309) + (0.6548 * 64314) and i can not get this formula. Being (P * Q) + (P * Q)

What can i do? i can´t find the way to do it.

Thanks Qlik community!!

pablo

sum distinct in a expression

Thu, 14 Jan 2010 10:01:59 GMT

Hi Pablo,

see the attached example. Hope it is what you want.

Good luck!

Rainer

sum distinct in a expression

Thu, 14 Jan 2010 19:41:58 GMT

thank you very much Rainer,

i tested what you´ve sent me but it doesn´t work. Please look at the next table...it´s been filtered from your QVW

CONV	SIGNAL	DATE	PRICE	QUANTITY	*Sum(Distinct QUANTITY) Sum(Distinct PRICE)**
12145	EL GOURMET	01/01/2010	0,0414	50146	2076,0444
12145	EL GOURMET	01/01/2010	0,0414	50309	2082,7926
12145	EL GOURMET	01/02/2010	0,0414	50146	2076,0444
12145	EL GOURMET	01/02/2010	0,0414	50309	2082,7926
12145	EL GOURMET	01/03/2010	0,0414	50146	2076,0444
12145	EL GOURMET	01/03/2010	0,0414	50309	2082,7926
12145	FILM & ARTS	01/01/2010	0,0414	50146	2076,0444
12145	FILM & ARTS	01/01/2010	0,0414	50309	2082,7926
12145	FILM & ARTS	01/02/2010	0,0414	50146	2076,0444
12145	FILM & ARTS	01/02/2010	0,0414	50309	2082,7926
12145	FILM & ARTS	01/03/2010	0,0414	50146	2076,0444
12145	FILM & ARTS	01/03/2010	0,0414	50309	2082,7926

as you can see in the 1st row, for CONV = 12145, Date = 01/01/2010 there is no data that matches with the Excel; this result matches with the same CONV (12145) but SIGNAL = FILM & ARTS. The 2nd row is OK.

The same happens to the SIGNAL = FILM & ARTS, where we have only the combination Price = 0.0414 and Quantity = 50146.

Sorry for my english, i hope you understand what i´m trying to tell you.

Thanks again,

pablo

sum distinct in a expression

Fri, 15 Jan 2010 09:35:14 GMT

Hi Pablo,

i hope it´s better now.

Rainer

sum distinct in a expression

Tue, 19 Jan 2010 15:52:21 GMT

hi Rainer,

it works; just one more question: do you know why it works only if i use a excel 2007 and it doesn´t work if i use an older version of excel?

thank you very much for your time,

pablo

sum distinct in a expression

Tue, 19 Jan 2010 16:07:05 GMT

Hi Pablo,

I´m not sure what do you mean by "it didn´t work with an older version of excel".

The difference, from qliview point of view, here is the prefix (2007: *.xlsx; 2003: *.xls).

Best wishes

Rainer

sum distinct in a expression

Wed, 20 Jan 2010 15:24:38 GMT

hi Rainer,

i think i´ve found the problem; it´s not about the excel version...it happens when you use filters to make the crosstable, for example to remove columns...it seems it´s not allowed to do.

thank you very much,

i hope you are OK, see you soon here...

pablo