topic how to add calculated column to chart in QlikView

how to add calculated column to chart

Wed, 03 Nov 2010 11:10:00 GMT

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 !

how to add calculated column to chart

llauses243 — Wed, 03 Nov 2010 18:09:43 GMT

Hi,

Try again with ...

Good luck, Luis

how to add calculated column to chart

johnw — Wed, 03 Nov 2010 22:37:31 GMT

This might work:

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

how to add calculated column to chart

Thu, 04 Nov 2010 06:52:02 GMT

hi,

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

how to add calculated column to chart

johnw — Thu, 04 Nov 2010 16:01:59 GMT

column(2)-column(1)

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

how to add calculated column to chart

Thu, 04 Nov 2010 17:19:15 GMT

hi,

wow 🙂 it works 🙂

THANK YOU !

how to add calculated column to chart

Mon, 26 Sep 2011 20:39:01 GMT

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

Alex

Re: how to add calculated column to chart

Thu, 15 Mar 2012 09:09:50 GMT

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

Re: how to add calculated column to chart

Fri, 04 Oct 2013 09:50:13 GMT

Excellent thanks!