i load this table as a crosstable, where 01/2010, 02/2010 and 03/2010 are DATE and the other columns are qualifiers.
|12145||FILM & ARTS||P||0,0414||0,0414||0,0414|
|12145||FILM & ARTS||Q||50.146||50.146||50.146|
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!!
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||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.
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,
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).
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...