topic Difference between two columns in a pivot chart in QlikView

Difference between two columns in a pivot chart

Thu, 04 Nov 2010 15:38:37 GMT

Hi,

I have a pivot chart with two dimensions and three expressions :

And I'd like to have a third column with the difference of the two first ones like this :

TRG_CODE DATE 200912 201007 Difference

NIE Encours M€ 131,2 142,8 11,6

CREELIA NB entreprise 1352 1374 22

....

How can I do that with my pivot table.

I attach the qvw

Thx for your help

Regards

Nicolas

Difference between two columns in a pivot chart

johnw — Fri, 05 Nov 2010 23:02:45 GMT

On the Presentation tab, show partial sums for DATE. Label it 'Difference'. Replace your current expressions with things like this:

if(dimensionality()=1
,only({<DATE={'$(=max(DATE))'}>} COLLECTES_NETTES)
-only({<DATE={'$(=min(DATE))'}>} COLLECTES_NETTES)
,COLLECTES_NETTES)

There might be a simpler solution, but it seems to work.

Difference between two columns in a pivot chart

Tue, 09 Nov 2010 18:17:34 GMT

Hi John,

Thx a lot, it works very well.

I have another question. How can I do the same thing without my "CODE" dimension :

DATE 200912 201007 Difference

Encours M€ 131,2 142,8 11,6

NB entreprise 1352 1374 22

because it doesn't work doing this :

if(dimensionality()=1
,only({<DATE={'$(=max(DATE))'}>} sum(EN_COURS))
-only({<DATE={'$(=min(DATE))'}>} sum(EN_COURS))
,sum(EN_COURS))

How can I aggregate the values on my "DATE" dimension (without the "CODE" dimension) and keep the difference between the two totals.

Thx a lot for you help.

Nicolas

Difference between two columns in a pivot chart

Thu, 25 Nov 2010 11:21:14 GMT

Hi everybody,

I'm new here and my english is very bad so....i'm sorry!!

I've the same problem as Nicolas : i need to do the difference between two columns (It looks like his exemple) but your solution doesn't work...(surely because i don't know how to do!) 😞

do you know where could i found an exemple or tutorial about the expression "only (...)" or something else which can help me??

Thanks a lot.

Maeva

Difference between two columns in a pivot chart

bismart — Thu, 25 Nov 2010 15:06:13 GMT

Create new expression as Column(2) - Column(1)

Difference between two columns in a pivot chart

Thu, 25 Nov 2010 15:10:48 GMT

So, I'm searching but i don't find!!

I think it doesn't work in my case because my expression (exemple EN_COURS) isn't an amount recovered directly in my database BUT an amount which comes from a calculation (exemple SUM(if(OPER_VAL< DayStart(DATE_VL_OPER,1) or IsNull(OPER_VAL) ,OPER_NB_PART * VA_VALEUR_EURO,0 )

Do you agree with me?

thanks for your help...

PS : here's what i tried

=if( dimensionality()=1

,only ({$<[Date Mise a jour VL]={"31/07/2010 23:59:00"}>}

(SUM(if(OPER_DT_VAL< DayStart(DATE_VL_OPER,1) or IsNull(OPER_DT_VAL) ,OPER_NB_PART * VA_VALEUR_EURO,0))))

- only ({$<[Date Mise a jour VL]={"31/12/2009 23:59:00"}>}

(SUM(if(OPER_DT_VAL< DayStart(DATE_VL_OPER,1) or IsNull(OPER_DT_VAL) ,OPER_NB_PART * VA_VALEUR_EURO,0))))

, (SUM(if(OPER_DT_VAL< DayStart(DATE_VL_OPER,1) or IsNull(OPER_DT_VAL) ,OPER_NB_PART * VA_VALEUR_EURO,0))))

Difference between two columns in a pivot chart

Thu, 25 Nov 2010 15:16:34 GMT

"column(2) - column(1)" doesn"t work because i have a dimension "DATE" and a expression "EN_COURS".

i have for exemple :

colum 1 : september,

colum 2 : october

I would like :

column 3 : difference for EN_COURS between column 1 and column 2.

I hope you'll understand what i explain!! 😉

Difference between two columns in a pivot chart

Fri, 26 Nov 2010 11:31:06 GMT

Hello,

After long search my problem has changed!!

My expression is :

=if(dimensionality()=1
,
(SUM(if(MonthEnd([Date Mise a jour VL]) = MonthEnd(DateDeSelection) and (OPER_DT_VAL< DayStart(DATE_VL_OPER,1) or IsNull(OPER_DT_VAL) ),OPER_NB_PART * VA_VALEUR_EURO,0)))
-
(SUM(if(MonthEnd([Date Mise a jour VL]) =MonthEnd(YearEnd(AddYears(DateDeSelection,-1))) and (OPER_DT_VAL< DayStart(DATE_VL_OPER,1) or IsNull(OPER_DT_VAL) ),OPER_NB_PART * VA_VALEUR_EURO,0)))
)

I get the following result :

Apporteur	Date	12/2009	07/2010	Variation
aa		-508 368,46 €	497 614,12 €	-10 754,34 €
bb		-3 585 396,53 €	3 634 405,24 €	49 008,71 €
cc		-4 490 400,79 €	4 399 782,08 €	-90 618,71 €
dd		-236 872,16 €	235 583,66 €	-1 288,50 €
ee		-2 491 218,79 €	2 439 177,91 €	-52 040,88 €
ff		-21 070 265,49 €	20 925 739,14 €	-144 526,35 €
gg		-1 689 665,93 €	1 678 919,54 €	-10 746,39 €

this pivot chart is good EXCEPT the sign befor values for 12/2009 :

i would like the same WITHOUT the minus sign which is before amount 12/2009 :

Apporteur	Date	12/2009	07/2010	Variation
aa		508 368,46 €	497 614,12 €	-10 754,34 €
bb		3 585 396,53 €	3 634 405,24 €	49 008,71 €
cc		4 490 400,79 €	4 399 782,08 €	-90 618,71 €
dd		236 872,16 €	235 583,66 €	-1 288,50 €
ee		2 491 218,79 €	2 439 177,91 €	-52 040,88 €
ff		21 070 265,49 €	20 925 739,14 €	-144 526,35 €
gg		1 689 665,93 €	1 678 919,54 €	-10 746,39 €

Do you know how can i do it ??

Thankkkkkk you 😉