topic Re: Column() function How and Where to use in QlikView

Column() function How and Where to use

Anonymous — Thu, 05 Feb 2015 17:34:35 GMT

Im trying to use the Column() function and it is not displaying or calculaing anything. I do not want to use the field name as this could change.
Im attaching a picture of the Straight table that I created. I want to do calculations on the difference bettween 2014-01 and 2014-03 (ulitmately I will have all year months calculated but for this test, Im ok with how the data looks) This data will be a rolling 18 months of data. In the Expressions I created a function which says Sum (Column(2) + Column(3)) It returns nothing. If I use this Sum([2014-01] + [2014-03]) I get the correct result. It wont even display andy data in Column(2) Any clue what I need to be doing differently. (Im very new to Qlikview)

Re: Column() function How and Where to use

giakoum — Thu, 05 Feb 2015 17:41:14 GMT

I presume you have year-month as dimension and only one expression.

so column will not work here as it refers to the expression so you have only Column one.

In order to do what you want, you need to use functions as peek, above etc.

Re: Column() function How and Where to use

Thu, 05 Feb 2015 17:42:04 GMT

I try

= Column(1)+Column(2)

Re: Column() function How and Where to use

Anonymous — Thu, 05 Feb 2015 17:48:04 GMT

Heres what it looks like

so no the year month is not a dimension. I ahve several columns. I did the pivot over in sqlserver and brought the data over as it looks flname 2014-01 2014-03 etc...(more year monnths) with a value of Production Per Hour in these columns))

Re: Column() function How and Where to use

Anonymous — Thu, 05 Feb 2015 18:21:26 GMT

i tried that but it didnt work (ive tried several options.... like that bummer its not working

Re: Column() function How and Where to use

Anonymous — Thu, 05 Feb 2015 18:45:22 GMT

this is what they have in excel and want in qv.... Ive never done a qv rpt so really have no clue where to start, Im assumming I need either a pivot table or a strait table. I can get the data where the P/C is by Year Month or I can pivot it in sql server and have the year months as columns when it comes to QV. How would you go about reproducing this rpt?

Re: Column() function How and Where to use

rwunderlich — Thu, 05 Feb 2015 18:57:35 GMT

According to the screenshots you posted, it's as Ioannis suggested. 2014-01 etc are Dimensions. The Column(n) function works only with chart Expression columns. When you use [2014-01] - [2014-03] you are referred to field names, not chart columns. You can make the fields Expressions instead of DImensions if you want to use Column().

-Rob

Re: Column() function How and Where to use

rwunderlich — Thu, 05 Feb 2015 19:01:13 GMT

Bring it in unpivoted.

1. Create a Pivot table chart.

2. Use DImensions Name, Month-Year.

3. Use expressions: =Production / Contribution.

4. Drag the Month-Year Column up to the right corner or the chart to make it horizontal.

Much easier to help if yo can post a small sample qvw.

-Rob

Re: Column() function How and Where to use

Anonymous — Thu, 05 Feb 2015 19:04:16 GMT

Yes that works!!!! Thanks so much Thanks for helping me understand.

Re: Column() function How and Where to use

Anonymous — Thu, 05 Feb 2015 19:06:04 GMT

Thanks as I will try this too! I never could get it to put the columns spread out(horizontal)

Beings new to this, I just don’t know the step by step process. Ill get there, thanks to your help!

Re: Column() function How and Where to use

Anonymous — Thu, 05 Feb 2015 20:11:37 GMT

I got the pivot table working Rob W, now to get the percentage change between the yearmonths would I need to use the Peek function as Im trying the Column() and its not calculating ( Column(2) - Column(3))/Column(2)) In fact it looks strange on the pivot table.

I would post the qvw but no clue exactly how ..... but heres a picture. Thanks so much for pointing me in a direction.. totally appreciate it

Re: Column() function How and Where to use

rwunderlich — Thu, 05 Feb 2015 20:33:10 GMT

If you want to calculate change from the prior month on the chart, use the before() function. Read up on before() and the other inter-chart functions in the Help (look for before in the Index).

-Rob

Re: Column() function How and Where to use

Anonymous — Fri, 06 Feb 2015 11:18:18 GMT

Thanks for pointing me in a direction!

Re: Column() function How and Where to use

Anonymous — Fri, 06 Feb 2015 15:36:22 GMT

Rob, Thanks so much I got the Before working and it looks great! Exactly what I wanted!