Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
If you are a Qlik Insight Bot user, join this collaborative group: JOIN GROUP
Not applicable

sum distinct in a expression

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

CONVSIGNALUNIT01/201002/201003-2010
14139

CANAL (A)

P0,23580,26480,6345
14139

CANAL (A)

Q50.30950.30950.309
12145EL GOURMETP0,04140,04140,0414
12145EL GOURMETQ50.30950.30950.309
12145FILM & ARTSP0,04140,04140,0414
12145FILM & ARTSQ50.14650.14650.146
14137

CANAL (A)

P0,65480,64860,6485
14137

CANAL (A)

Q64.31464.31464.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

6 Replies
Highlighted
Not applicable

sum distinct in a expression

Hi Pablo,

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

Good luck!

Rainer

Highlighted
Not applicable

sum distinct in a expression

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

CONVSIGNALDATEPRICEQUANTITYSum(Distinct QUANTITY) * Sum(Distinct PRICE)
12145EL GOURMET01/01/20100,0414501462076,0444
12145EL GOURMET01/01/20100,0414503092082,7926
12145EL GOURMET01/02/20100,0414501462076,0444
12145EL GOURMET01/02/20100,0414503092082,7926
12145EL GOURMET01/03/20100,0414501462076,0444
12145EL GOURMET01/03/20100,0414503092082,7926
12145FILM & ARTS01/01/20100,0414501462076,0444
12145FILM & ARTS01/01/20100,0414503092082,7926
12145FILM & ARTS01/02/20100,0414501462076,0444
12145FILM & ARTS01/02/20100,0414503092082,7926
12145FILM & ARTS01/03/20100,0414501462076,0444
12145FILM & ARTS01/03/20100,0414503092082,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

Highlighted
Not applicable

sum distinct in a expression

Hi Pablo,

i hope it´s better now.

Rainer

Highlighted
Not applicable

sum distinct in a expression

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

Highlighted
Not applicable

sum distinct in a expression

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

Highlighted
Not applicable

sum distinct in a expression

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