Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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
Create new expression as Column(2) - Column(1)
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))))
"column(2) - column(1)" doesn"t work because i have a dimension "DATE" and a expression "EN_COURS".
i have for exemple :
I would like :
I hope you'll understand what i explain!! 😉
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 😉