Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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))


13 Replies
Not applicable
Author

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.

villafuerte
Partner Ambassador
Partner Ambassador

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!

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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?

Not applicable
Author

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%

Hope, it can help you

JJ

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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.