calculate rate

[#1]

Hi,

I need an expression to calculate the rate between 2 values.

With another expression I calculate the total sales amount in different year. For example I have a pivot like this

Country	2007	2008
France	1000	2000
Italy	100	500
Spain	500	300

Now I need to add a colum for the rate.. Example

Country	2007	2008	Rate
France	1000	2000	+100 %
Italy	100	500	+11%
Spain	500	300	-40%

If someone can explain with an example application, it would be greatful.

Thanks

[#2]

You want to calculate a rate between column 1 and column 2.

I would add an expression such as

(Column(2) - column(1)) / column(1).

However you must take in account what to do if your first column (2007) is 0. In this case you will have a null value. I would add something like:

IF(column(1)=0,1,(Column(2) - column(1)) / column(1))

Hope this is ok for your need

Rgds

Sébastien

[#3]

Thanks for the quickly reply,

My problem is how can I select the column, because these are not fixed column but they are calcolated too and when I add the expression to calculate rate it duplicates the value. I post an image to explain better

I need only one column for the rate and without year.

I hope you can help me, thanks a lot

Rick

[#4]

In this case I would replace the column(1) by the expression you used and same for column(2)

Example :(sum($<{Year={2008}> sales) - sum($<{Year={2007}> sales)) / sum($<{Year={2007}> sales)

Is it ok for you ?

[johnw · Accepted solution]

I think there's a little confusion here. To clarify a bit, Rick is using a pivot table, and spastor's suggestion involves a straight table.

One solution IS to turn the pivot table into a straight table using expressions such as those spastor suggests. Another solution is to have a Rate for each year by adding another expression to the pivot table using the before() function. I've attached an example with both solutions.

[#6]

Great, thanks a lot.

I use the pivot table, otherwise I must change the expression every time I want compare two different year.

Last question,..Can you explain what is the before function??

if you can't it's tha same. I don't want to stress you anymore. Thanks a lot

Rick

[#7]

The Before(expression) function displays the value of the expression for the dimensions on the line above. Here's the definition from the reference manual:

Returns the value of expression evaluated with a pivot table's
dimension values as they appear in the column before the current
column within a row segment in the pivot table. This function
returns NULL in all chart types except pivot tables.

See page 365 of Book III in the Reference Manual for the complete definition.

[johnw]

Well, the column to the left, not the line above, but yes. So since we have our years laid out left to right, before() tells QlikView to use the value from the previous year.