topic rangeavg in QlikView

rangeavg

Mon, 18 Jan 2010 13:40:38 GMT

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

Mon, 18 Jan 2010 14:14:31 GMT

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

villafuerte — Mon, 18 Jan 2010 16:13:19 GMT

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

Mon, 18 Jan 2010 18:55:09 GMT

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

rangeavg

Tue, 19 Jan 2010 06:38:09 GMT

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.

rangeavg

Tue, 19 Jan 2010 07:01:10 GMT

I am able to understand this part of your post:

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

Tue, 19 Jan 2010 09:42:13 GMT

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%

Hope, it can help you

rangeavg

Thu, 21 Jan 2010 14:31:17 GMT

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

password=c2info99

Regards,

Rikab

rangeavg

Thu, 21 Jan 2010 20:19:55 GMT

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

rangeavg

Fri, 22 Jan 2010 06:30:18 GMT

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.

Anyway thanks for your help. Hope to receive your reply soon.

rangeavg

Fri, 22 Jan 2010 09:32:04 GMT

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

rangeavg

Fri, 22 Jan 2010 10:45:22 GMT

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

Thu, 16 Sep 2010 05:18:26 GMT

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

Thu, 16 Sep 2010 20:31:34 GMT

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