Average sales expression over selection

[#1]

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.

[kaushiknsolanki]

Hi,

You can use the Avg() function.

Regards,

Kaushik Solanki

[MK_QSL]

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

[#4]

Good day Kaushik.

Thank you for your reply.

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

[#5]

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.

[MK_QSL]

Try this

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

[#7]

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

[rido1421 · Accepted solution]

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

[MK_QSL]

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

[#10]

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.