topic Re: sum problem with pivot table in QlikView

sum problem with pivot table

aminehaddar — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have this table:

COMMERCIAL	Emploi	PRODUITS	REALISATION	score	SCORE_AVEC_PLAFOND
001	CCPP	x1	331568	66314	1667
	CCPP	x2	84576	42288	1667
			416144	66314	66314
002	CCPP	x2	4752	950	950
	CCPP	x3	260579	130289	1667
			265331	950	950

I have a problem with the sum here.

Can you explain why I got this mistake?

and anyone that can help me to resolve this issue please?

Thanks

Regards,

Re: sum problem with pivot table

sunny_talwar — Mon, 18 Jun 2018 15:40:56 GMT

What is the expression that you are using?

Re: sum problem with pivot table

aminehaddar — Mon, 18 Jun 2018 15:45:20 GMT

REALISATION= sum(VALEUR)

score=

if(

if(IsNull(score_periode1), sum(score_periode2*VALEUR),sum(score_periode1*VALEUR))

<0,

if(IsNull(score_periode1), sum(score_periode2*VALEUR),sum(score_periode1*VALEUR))

)

SCORE_AVEC_PLAFOND=

if(match(REF,'19.1','377') and score>2000*(5/6), 2000*(5/6), score)

Re: sum problem with pivot table

sunny_talwar — Mon, 18 Jun 2018 15:56:59 GMT

May be try this

score=

Sum(Aggr(

if(

if(IsNull(score_periode1), sum(score_periode2*VALEUR),sum(score_periode1*VALEUR))

<0,

if(IsNull(score_periode1), sum(score_periode2*VALEUR),sum(score_periode1*VALEUR))

)

, COMMERCIAL, Emploi, PRODUITS))

SCORE_AVEC_PLAFOND=

Sum(Aggr(

if(match(REF,'19.1','377') and

Sum(Aggr(

if(

if(IsNull(score_periode1), sum(score_periode2*VALEUR),sum(score_periode1*VALEUR))

<0,

if(IsNull(score_periode1), sum(score_periode2*VALEUR),sum(score_periode1*VALEUR))

)

, COMMERCIAL, Emploi, PRODUITS))

>2000*(5/6), 2000*(5/6),

Sum(Aggr(

if(

if(IsNull(score_periode1), sum(score_periode2*VALEUR),sum(score_periode1*VALEUR))

<0,

if(IsNull(score_periode1), sum(score_periode2*VALEUR),sum(score_periode1*VALEUR))

)

, COMMERCIAL, Emploi, PRODUITS))

)

, COMMERCIAL, Emploi, PRODUITS))

Re: sum problem with pivot table

aminehaddar — Mon, 18 Jun 2018 16:09:22 GMT

excellent bro. thanks a lot

Re: sum problem with pivot table

vishsaggi — Mon, 18 Jun 2018 16:11:38 GMT

Can we write this way sunny?

score=

Sum(Alt(score_periode1, scoreperiode2)*VALEUR)

SCORE_AVEC_PLAFOND=

if(match(REF,'19.1','377') and Sum(Alt(score_periode1, scoreperiode2)*VALEUR) > 2000*(5/6), 2000*(5/6), Sum(Alt(score_periode1, scoreperiode2)*VALEUR))

Re: sum problem with pivot table

sunny_talwar — Mon, 18 Jun 2018 16:21:26 GMT

I think there is a check for less than 0 also... may be like this

Sum(Aggr(RangeMax(Sum(Alt(score_periode1, scoreperiode2)*VALEUR), 0), COMMERCIAL, Emploi, PRODUITS))

Re: sum problem with pivot table

vishsaggi — Mon, 18 Jun 2018 16:21:37 GMT

Hey Amine,

can you also try this and let us know if this works too?

score=

Sum(Aggr(RangeMax(Sum(Alt(score_periode1, scoreperiode2)*VALEUR), 0), COMMERCIAL, Emploi, PRODUITS))

SCORE_AVEC_PLAFOND=

Sum(Aggr(

if(match(REF,'19.1','377') and

Sum(Aggr(RangeMax(Sum(Alt(score_periode1, scoreperiode2)*VALEUR), 0), COMMERCIAL, Emploi, PRODUITS)) > 2000*(5/6), 2000*(5/6),

Sum(Aggr(RangeMax(Sum(Alt(score_periode1, scoreperiode2)*VALEUR), 0), COMMERCIAL, Emploi, PRODUITS))

)

, COMMERCIAL, Emploi, PRODUITS))

Re: sum problem with pivot table

vishsaggi — Mon, 18 Jun 2018 16:22:17 GMT

Oh i missed that. Got it. Thank you.