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.

[chenriot]

I'm sorry but I don't understand what I have to do.

[Anil_Babu_Samineni]

Would you like to share sample to test?

[stabben23]

Hi,

ok I'll think this part is not a problem

(

(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) )
)),

Cant see any problem here.

The opening part of the Expression is what I think cause Your problem.

sum(aggr((If($(SommeQté)<>0 OR GetSelectedCount(%_Etablissement_Filliale) = 0,

is there a possibility to move this part into the set analysis?

GetSelectedCount(%_Etablissement_Filliale)=0 feels like it could.

could you show us whats inside this variable (SommeQté)

[chenriot]

Hi,

The variable (SommeQté) is :

='If(GetSelectedCount(%_QtéA0)>0,
If(AffFR,sum({<UCDIVI={"1A0"},UCIVDT = {">$(DATE_REF_1_YYYYMMDD)<=$(DATE_REF_YYYYMMDD)"}> } UCIVQT),0)
+ If(AffEXP,sum({<UCDIVI={"1B0"},UCIVDT = {">$(DATE_REF_1_YYYYMMDD)<=$(DATE_REF_YYYYMMDD)"}> } UCIVQT),0)
+ If(AffUK, sum({<UCCUNO={A203001},UCIVDT = {">$(DATE_REF_1_YYYYMMDD)<=$(DATE_REF_YYYYMMDD)"}> } UCIVQT),0)
+ If(AffUSA, sum({<UCCUNO={A223001},UCIVDT = {">$(DATE_REF_1_YYYYMMDD)<=$(DATE_REF_YYYYMMDD)"}> } UCIVQT),0)
+ If(AffUSCMS, sum({<UCCUNO={A222001},UCIVDT = {">$(DATE_REF_1_YYYYMMDD)<=$(DATE_REF_YYYYMMDD)"}> } UCIVQT),0)
+ If(AffKG, sum({<UCCUNO={A201001},UCIVDT = {">$(DATE_REF_1_YYYYMMDD)<=$(DATE_REF_YYYYMMDD)"}> } UCIVQT),0)
+ If(AffITALIA, sum({<UCCUNO={A213001},UCIVDT = {">$(DATE_REF_1_YYYYMMDD)<=$(DATE_REF_YYYYMMDD)"}> } UCIVQT),0)
+ If(AffESPANA, sum({<UCCUNO={A204001},UCIVDT = {">$(DATE_REF_1_YYYYMMDD)<=$(DATE_REF_YYYYMMDD)"}> } UCIVQT),0)
+ If(AffNEDERLAND, sum({<UCCUNO={A207001},UCIVDT = {">$(DATE_REF_1_YYYYMMDD)<=$(DATE_REF_YYYYMMDD)"}> } UCIVQT),0)
+ If(AffINDIA, sum({<UCCUNO={A209001},UCIVDT = {">$(DATE_REF_1_YYYYMMDD)<=$(DATE_REF_YYYYMMDD)"}> } UCIVQT),0)
+ If(AffBELGIUM, sum({<UCCUNO={A202001},UCIVDT = {">$(DATE_REF_1_YYYYMMDD)<=$(DATE_REF_YYYYMMDD)"}> } UCIVQT),0)
+ If(AffPORTUGAL, sum({<UCCUNO={A208000},UCIVDT = {">$(DATE_REF_1_YYYYMMDD)<=$(DATE_REF_YYYYMMDD)"}> } UCIVQT),0)
+ If(AffIRELAND, sum({<UCCUNO={A205001},UCIVDT = {">$(DATE_REF_1_YYYYMMDD)<=$(DATE_REF_YYYYMMDD)"}> } UCIVQT),0)
+ If(AffCOLUMBIA, sum({<UCCUNO={A211001},UCIVDT = {">$(DATE_REF_1_YYYYMMDD)<=$(DATE_REF_YYYYMMDD)"}> } UCIVQT),0)
+ If(AffSWEDEN, sum({<UCCUNO={A212001},UCIVDT = {">$(DATE_REF_1_YYYYMMDD)<=$(DATE_REF_YYYYMMDD)"}> } UCIVQT),0)
+ If(AffPOLSKA, sum({<UCCUNO={A217001},UCIVDT = {">$(DATE_REF_1_YYYYMMDD)<=$(DATE_REF_YYYYMMDD)"}> } UCIVQT),0)
+ If(AffSWITZERLAND, sum({<UCCUNO={A214001},UCIVDT = {">$(DATE_REF_1_YYYYMMDD)<=$(DATE_REF_YYYYMMDD)"}> } UCIVQT),0)
+ If(AffURUGUAY, sum({<UCCUNO={A210001},UCIVDT = {">$(DATE_REF_1_YYYYMMDD)<=$(DATE_REF_YYYYMMDD)"}> } UCIVQT),0)
+ If(AffTCHEQUIE, sum({<UCCUNO={A219001},UCIVDT = {">$(DATE_REF_1_YYYYMMDD)<=$(DATE_REF_YYYYMMDD)"}> } UCIVQT),0)
+ If(AffNORVEGE, sum({<UCCUNO={A224001},UCIVDT = {">$(DATE_REF_1_YYYYMMDD)<=$(DATE_REF_YYYYMMDD)"}> } UCIVQT),0)
+ If(AffJAPON, sum({<UCCUNO={A225001},UCIVDT = {">$(DATE_REF_1_YYYYMMDD)<=$(DATE_REF_YYYYMMDD)"}> } UCIVQT),0)
+ If(AffDENMARK, sum({<UCCUNO={A216001},UCIVDT = {">$(DATE_REF_1_YYYYMMDD)<=$(DATE_REF_YYYYMMDD)"}> } UCIVQT),0)
+ If(AffBULGARIA, sum({<UCCUNO={A218001},UCIVDT = {">$(DATE_REF_1_YYYYMMDD)<=$(DATE_REF_YYYYMMDD)"}> } UCIVQT),0)
+ If(AffPEROUSE, sum({<UCCUNO={A228001},UCIVDT = {">$(DATE_REF_1_YYYYMMDD)<=$(DATE_REF_YYYYMMDD)"}> } UCIVQT),0)
+ If(AffTURKEY, sum({<UCCUNO={A226001},UCIVDT = {">$(DATE_REF_1_YYYYMMDD)<=$(DATE_REF_YYYYMMDD)"}> } UCIVQT),0)
+ If(AffFINLAND, sum({<UCCUNO={A227001},UCIVDT = {">$(DATE_REF_1_YYYYMMDD)<=$(DATE_REF_YYYYMMDD)"}> } UCIVQT),0)
, 1
)
'

[stabben23]

wow, this gives me headache, why is this needed? What do you try to achieve With your expression?

[chenriot]

Hi,

I'm sorry for the headache !

In the application, we have the possibility to hide or to show lines in the table regarding the quantity of each  subsidiaries. It deals with the flag "%_QtéA0" which is switch on when we want to hide the lines with the quantity=0.

Then we have the possibility to hide or to show columns for each subsidiaries. So if you want to see only values for France, we must only show the lines where the quantities are more than 0 for France. We have one flag for each subsidiaries that called "Aff..." (ex. AffFR for France).

I try to make an application for example.

[stabben23]

Why not handle this in script when the Field seams to be from OSBSTD table. If you create a New Field in script With all Your if statment, then memory consumption in chart will reduce a lot I think.

Then you use the New Field in set analysis instead of all if's.

[chenriot]

I don't know how to do in the script because it dépends on what it's selected for both the subsidiaries and for the quantities to 0.

I try to do on an other way.

Thanks for your help