Problem with total of percentage

[chenriot]

Hello,

I work with Qlikview 11.

I have a problem with the result of the total expression when it corresponds to a percentage calculation.

Here my example :

Calculation of %Margin = (Revenue - Cost)/Revenue

When I use the "Average" calculation for the total of the expression, I obtain 35.61% but I would like 30.67% which is obtained with the total values of Cost and Revenue (see green information).

Can someone help me to solve this problem ?

Thanks in advance.

[sunny_talwar]

Is this in the straight table? May be try to use Expression Total instead of Avg Total Mode

[stabben23]

do you use any aggregation like sum in Your Expression?

sum((Revenue) - sum(Cost))/sum(Revenue)?

[chenriot]

Yes I use a straight table. I can't use Total because QV makes the sum of percentage.

[sunny_talwar]

Really? Can you share a sample to show this issue?

[chenriot]

In fact, the expression is like this :

if (qty <>0,

(
(sum({<Date_fact = {">$(DATE_REF)<=$(DATE_REF)"}>} Revenue) 
) - ( Unit_cost * sum({< Date_fact = {">$(DATE_REF)<=$(DATE_REF)"} >} Qty ) ) )
  /
  (sum({<Data_fact = {">$(DATE_REF)<=$(DATE_REF)"}>} Revenue) )
))

DATE_REF is selected in a calendar by the user.

Date_fact is the invoice date

Unit_cost is a variable defined like this :

MAX({< begining_date = {">=$(DATE_REF)"}, end_date = {"<=$(DATE_REF_YYYYMMDD)"}, price_type={"80"}>} PRICE)

[Peter_Cammaert]

There may be side-effects at play in your document. Better post an example document that exhibits this weird behavior.

[stabben23]

Hi Christelle,

Your problem is that you start the Expression With an if. This will work on every row in Your chart, but in total you will have all values regardless of Your if statment if (qty <>0. One way to solve this is to use aggr() function.Use the Dimension from where you get A,B,C... Give this Expression a try, remember to change to Your used Dimension.

sum(aggr(if (qty <>0,

(
(sum({<Date_fact = {">$(DATE_REF)<=$(DATE_REF)"}>} Revenue) 
) - ( Unit_cost * sum({< Date_fact = {">$(DATE_REF)<=$(DATE_REF)"} >} Qty ) ) )
  /
  (sum({<Data_fact = {">$(DATE_REF)<=$(DATE_REF)"}>} Revenue) )
)) ,Dimension))

[chenriot]

Hi Staffan,

thanks for your help.

I try your expression but it does'nt work.

When I use the "total of expression" option or  the "sum of lines", I obtain the sum of percentages.

I write :

=sum(aggr((If($(SommeQté)<>0 OR GetSelectedCount(%_Etablissement_Filliale) = 0,
(
(sum({<UCIVDT = {">$(DATE_REF_1_YYYYMMDD)<=$(DATE_REF_YYYYMMDD)"}>} UCUCOS) 
) - ( $(CSSN) *  sum({< UCIVDT = {">$(DATE_REF_1_YYYYMMDD)<=$(DATE_REF_YYYYMMDD)"} >} UCIVQT ) ) )
  /
  (sum({<UCIVDT = {">$(DATE_REF_1_YYYYMMDD)<=$(DATE_REF_YYYYMMDD)"}>} UCUCOS) )
)),
[Code article] ))

[stabben23]

ok,

Expression total is to prefer here I'll think.