13 Replies Latest reply: Sep 16, 2010 4:31 PM by Jean-Jacques Jesua

# rangeavg

Hi All,

Can any one explain me how this expression works. I had gone through the help but i am not able to understand. Thankful to you if some can explain here with the data and calculation here so that it is easy for newbie like me.

`rangeavg(above(Sum(SalValue),0,3))`

• ###### rangeavg

Hi All,

Confirm me whether the way i understand is correct or not. Let the sales value is like below.

SalValue

10,000

20,000

30,000

This is the way i understand:

above(Sum(SalValue),0,3) = above((10,000+20,000+30,000),0,3)

= 60,000/3

= 20,000

Let me know whether i can understanding in the right way.

• ###### rangeavg

Hi,

I thik that the problem can be that SalValue is a single value, for example, if you only have a table:

SaleVal

10,000

20,000

30,000

Then, the part of Sum(SalValue) will always be 60,000 (10,000+20,000+30,000).

So the expression is calculated like this:

rangeavg(above(Sum(SalValue),0,3)) = ((60,000+60,000+60,000)/3) = 60,000

Good luck!

• ###### rangeavg

Hi

The most important thing to understand with above is for a value of the dimension, to catch previous value of the dimension. So in your example, you want to calculate for a year (dimension), the average of sales from the 3 previous year.

For example if you want to show an evolution in a chart you must use above

- dimension = year

- expression = sum(sales) / above( sum(sales)) - 1 => % of evolution of sales from previous year

jj

• ###### rangeavg

Hi

I am able to understand this part of your post:

Hi

The most important thing to understand with above is for a value of the dimension, to catch previous value of the dimension. So in your example, you want to calculate for a year (dimension), the average of sales from the 3 previous year.

For example if you want to show an evolution in a chart you must use above

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

But I am not able to understand this part of the post:

For example if you want to show an evolution in a chart you must use above

- dimension = year

- expression = sum(sales) / above( sum(sales)) - 1 => % of evolution of sales from previous year

Can explain this with some sample data?

• ###### rangeavg

Hi

Please find an example with the use of ABOVE, where i display in a chart the evolution of the number of orders by year

In 2005, i have 1 order, in 2006 5 orders, so the evolution is 5/1 - 1 = +400%

JJ

• ###### rangeavg

Hi JJ,

I want to do the same thing for my document also but i am not been able to write the correct expression. Hope you can help me out. My requirement is as follows.

I want to display in a chart the evolution of Sales value(SalValue) by month. But didn't got succeeded in the doing it. I think there is something wrong with the expression. Hope if you look into my chart you can give solution.

Sales Value=SalValue and Month=MonthName.

userid=0010012021

Regards,

Rikab

• ###### rangeavg

Hi Rikab,

I'm not sure to understand well.your chart looks good but it displays the evolution from month to month without take into consideration the Year, so your dimension is the sum(salvalue) for each year.

I think you should use monthname(Date) as expression instead of Monthname

regards

jj

• ###### rangeavg

Hi JJ,

Sorry by mistake instead of considering the monthname(Date) I used only MonthName. Now i think is shows the correct value when i use your formula 5/1-1. By the way let me know is the right way to calculate the evoluation. Explain little bit!

Because i felt that this is the right formula! ((current month's sales - last month's sales)/last month's sales)x10. Let me know whether is correct or not.

• ###### rangeavg

hi

of course, it's the right formula for evolution & the same formula as you.

((current month's sales - last month's sales)/last month's sales) = current month's sales/ last month's sales - 1

Why do you multiplicate by 10 (maybe you mean by 100) but you don't need, use "show in percentage (%)' in the chart properties number.

regards

jj

• ###### rangeavg

Hi JJ,

I agreed! Now i am able to recall my maths now. The formula is same as yours and probably yours will be easier to apply. Thanks a ton!

Regards,

Rikab

• ###### rangeavg

Hi JJ,

I need your help in the below post. Issue is very similar to this post. Requesting you to help me out.

http://community.qlik.com/forums/t/34086.aspx

• ###### rangeavg

Hi Rikab,

It looks too tricky for me.

Have a look at this post : http://community.qlik.com/forums/t/33689.aspx

John W suggests when you want to make comparison Y2Y to merge the current year with the previous year (of course with a lag of 12 months).

It should simplify the syntax in the chart.

Hope it helps you.

regards

jj

• ###### rangeavg

Hi Villafuerte!

i agreed with this point

60,000 (10,000+20,000+30,000)

But i am not able to understand what that your are trying to say there

rangeavg(above(Sum(SalValue),0,3)) = ((60,000+60,000+60,000)/3) = 60,000

See the post above from Jean-Jacques Jesua. I am able to understand what he has meant. Hope that also helps you.