Show average value for weekday

[#1]

Hi,

I have a simple bar chart that displays the sum of sales by day. I would like to add, in the same chart, a line for the average value for each day of the week (average sales for Mondays, Tuesdays, ...). I am able to get the average values in a separate listbox (or chart) having the dayOfWeek as dimension, but can't find a way to add them to the daily sales chart.

Is there a way to do that?

Thank you

Jean

[swuehl]

Maybe like attached?

Dimension:

Date

Expression 1:

=sum(Value)

Expression 2:

= Only( Aggr( Avg(Total<Weekday> Value), Weekday, Date))

[#3]

Thanks for your reply.

The expression you provided computes the average sale per weekday. I am looking for the average total sales per weekday,meaning the average of the sum of sales for each weekday. I think the problem is in the avg function. I tried to use Sum instead and then divide by the number of occurrences of the weekday in the current date range selection, but couldn't find a way to get the number of occurrences.

Any ideas?

Btw, I am using personal edition, so I can't open attached qlikview documents..

[swuehl · Accepted solution]

I see, an appropriate modification could be

Only( Aggr(  Sum(Total<Weekday> Value) / count(Total<Weekday> DISTINCT Date) , Weekday, Date))

[#5]

Yes, that's exactly what I am looking for.

Thanks a lot.

[#6]

Thank you for being awesome    

[Abhi999]

I am trying with same expression(Only( Aggr(  Sum(Total<Weekday> Value) / count(Total<Weekday> DISTINCT Date) , Weekday, Date))) for last six weeks. but unfortunately we are not getting expected result.

cloud you help me for last 6 weeks same day avg.

Example:  today beeing monday i want to calculate what will be the avg of last 6 mondays, same for other days.

Trying to show combo chart, in combo chart bars i want to show actulas and in trend line i want to show if it monday last 6 monday's avg, if it tues day last 6 tuesday's avg.