topic Re: Show average value for weekday in QlikView

Show average value for weekday

Mon, 24 Aug 2015 14:05:54 GMT

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

Re: Show average value for weekday

swuehl — Mon, 24 Aug 2015 19:13:18 GMT

Maybe like attached?

Dimension:

Date

Expression 1:

=sum(Value)

Expression 2:

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

Re: Show average value for weekday

Tue, 25 Aug 2015 08:30:17 GMT

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

Re: Show average value for weekday

swuehl — Tue, 25 Aug 2015 18:46:27 GMT

I see, an appropriate modification could be

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

Re: Show average value for weekday

Wed, 26 Aug 2015 07:43:01 GMT

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

Thanks a lot.

Re: Show average value for weekday

Thu, 16 Jun 2016 14:55:08 GMT

Thank you for being awesome

Re: Show average value for weekday

Abhi999 — Mon, 10 Aug 2020 11:42:35 GMT

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.