how to add calculated column to chart

[#1]

hi,

i have created simple 1 dimension chart, and i would like to have a 3 column in Chart, showing the difference between Year 2009 and 2010

is it possible so ?

i have tried "[Suma] - before([Suma]), but it not works. i'm the beginer ... so i do understand that i no dot understand .. 🙂

Thanks !

[llauses243]

Hi,

Try again with ...

Good luck, Luis

[johnw]

This might work:

Expression 1 = sum({<Year={'$(=max(Year))'}>} Suma)
Expression 2 = sum({<Year={'$(=max(Year)-1)'}>} Suma)
Expression 3 = column(1) - column(2)

[#4]

hi,

it works, only the Question how to reverse minus column 🙂

[johnw · Accepted solution]

column(2)-column(1)

And remove Year as a dimension. That's now being handled by the expressions.

[#6]

hi,

wow 🙂 it works 🙂

THANK YOU !

[#7]

This does not seem to be working in QV 11. I wonder if something changed?

Alex

[#8]

Hello Sir,

I've a new problem of calculating differences of two cols based on some condition.

I've two tables: 1^st  having dimensions as Dim 1--> Dim 2-->Dim 3.....they've hierarchial relation as we represent in a pivot table.

But here we're concerned only wid Dim 3….the expr is Exp 1…Table1.xlsx is attached

Now the other is an excel file in crosstable format having sheets named 2011 & 2012 (Years) having

Cols Dim 3 & expr2…Table2.xlsx is attached….the script is below

Crosstable(Month,expr2,2)LOAD $(sheetname) as Year,
Dim3,

     January,
     February,
     March,
     April,
     May,
     June,
     July,
     August,
     September,
     October,
     November,
     December

     FROM '$(file)' (ooxml, embedded labels,table is $(sheetname));

I’m fetching them in QV & need to calculate the third expression : expr3 as

Expr3=if(expr1-expr2 for feb 2012>0,30 (expr1/expr2)*30)

Calculate Expr4 if  Expr3=30: Expr4= if(expr1-(expr2 for feb 2012 + expr2 for Jan2012)>0,30,(expr1-expr2 for Feb2012)/(expr2 for Jan 2012)*30)

Calculate Expr5 if Expr4=30: Expr5=if (expr1-(expr2 for feb 2012+expr2 for Jan2012 +expr2 for Dec2011)>0,30,(expr1-( expr2 for feb 2012+expr2 for Jan2012))/(expr2 for Dec 2011)*30

And so on……till expr3/expr4/expr5 is less than 30...and as soon as we get any of them less than 30 then add another col showing the sum:

for eg….expr3=30,expr4=30 and expr5=29, so Sum col=expr3+expr4+expr5

where & how  shud this logic be put……in script or in GUI expressions……

Plz help

Thanks & Regards

[#9]

Excellent thanks!