11 Replies Latest reply: Nov 29, 2016 7:55 AM by Brendt Wooi

# Average sales expression over selection

Good day.

i am hoping that somebody could help me, i am very new to Qlikview and have only just started using it.

we have setup a straight table with sales and budget information/columns. one of the requirements is to have a column with the average sales for the current year when the year is selected. but they would also like to average to change as they change the selections (eg. Month or day selected)

the expression i'm currently using is Sum or current year sales divided by 12. is that the correct expression? but im not sure how this would work when month or day is selected.

any help with this would be greatly appreciated.

• ###### Re: Average sales expression over selection

Hi,

You can use the Avg() function.

Regards,

Kaushik Solanki

• ###### Re: Average sales expression over selection

Good day Kaushik.

Avg() doesn't seem to yield the correct results. my sales figures are down to the line level, so Avg() seems to give average per line and not average sales per site. my initial expression seems correct to sum up all sales and divide by 12 to get the average per month, just not sure how to get the correct average if a particular month or day is selected.

this is the expression that i am currently using.

sum(if([Fin Year]=zMaxYear,Amount*_IsActual))/12

• ###### Re: Average sales expression over selection

What are the dimensions you are having in your chart/table where you want to show this ?

• ###### Re: Average sales expression over selection

Good day Manish.

we are just using a Site dimension. the table is showing sales per site. and i need to display average sales for current year per site.

• ###### Re: Average sales expression over selection

Try this

Avg(Aggr(sum(if([Fin Year]=zMaxYear,Amount*_IsActual)),Site))

• ###### Re: Average sales expression over selection

good day Manish.

Thank you for your suggestion, however that expression seems to give me the same value of my current total sales for the year.

would my initial expression not be correct to calculate the monthly average?

sum(if([Fin Year]=zMaxYear,Amount*_IsActual))/12

• ###### Re: Average sales expression over selection

Hi Brendt

Do you have a month field in your data?

instead of dividing by 12, rather divide by the count of the distinct months so... Count(Distinct Monthfield)

if you have not selected anything you will have 12 distinct months, if you select 3 months the average would be over the 3 months...

• ###### Re: Average sales expression over selection

Better if you provide few lines of sample data or sample app to work. It is difficult to guess and answer.

• ###### Re: Average sales expression over selection

Good day.

thank you all so much.

i have added the Count(Distinct Monthfield) expression suggested by Ridhaa to get the average for the year. i have also added a if into the expression so that if there is only one month selected that it would then divide by 365 days to get the average per day.

hope this will be correct.

• ###### Re: Average sales expression over selection

Hi Brendt

Remember to mark as correct if you have been assisted so that it can be closed :-)

• ###### Re: Average sales expression over selection

Hi Ridhaa.

Thank you so much. i have marked it.

Thank you so much to everyone who has helped me with this, i really do appreciate it.

hopefully i will become a Qlikview master like all you fantastic people on the forum one day.