topic Re: Weekly Average Problem in QlikView

Weekly Average Problem

Wed, 04 Jan 2012 16:34:34 GMT

Hi, I am trying to get an average of sales per week for each person. My dimension is Name.

My problem is that if I filter for December 2011 and Bob only had sales for 2 out of the 4 weeks in December then the formula only uses those 2 weeks he had data for. I want the expression to take his total sales for the month of the December and provide me a weekly average regardless how many weeks he actually submitted a sale during the month of December.

Any help will be greatly appreciated! Thanks!

count(distinct SaleId))/count(distinct Friday)

Weekly Average Problem

swuehl — Wed, 04 Jan 2012 16:44:10 GMT

If any of the persons worked in each week of the month, you could try:

count(distinct SaleId))/count(total distinct Friday)

Probably not the best / most performant way of doing it, so maybe we want to calculate the amount of weeks differently, is amount of Fridays your "official" definition?

Re: Weekly Average Problem

Wed, 04 Jan 2012 16:47:24 GMT

Yes, it is. Friday is the official definition of my business week. Friday-Thursday.

Re: Weekly Average Problem

swuehl — Wed, 04 Jan 2012 23:54:01 GMT

Since the amount of Fridays in a given month is static, I think it would be best to add the number of Fridays per Month in the data model, maybe along these lines:

Calendar:

LOAD *

,day(Date) as DayOfMonth

,weekday(Date) as Weekday

,Date(monthstart(Date),'YYYY-MMM') as Month

,if(WeekDay(Date)=4,Date) as FridayDate

;

LOAD

date(makedate(2011,1,1)+recno()-1) as Date

AUTOGENERATE 365

;

Fridays:

LOAD Month

,count(FridayDate) as NumFridays

Resident Calendar group by Month;

Then your expression could maybe look like:

=count(distinct SaleId) / sum(NumFridays)

edit: simplified Fridays table

Re: Weekly Average Problem

swuehl — Thu, 05 Jan 2012 12:29:11 GMT

One more thought:

Even though your business weeks start on Fridays, it may be worth considering calculating the amount of weeks in a Month not based on Fridays.

For example, look at April 2011, we get 5 Fridays / business weeks with 21 working days (assuming Mon-Fri).

For May 2011, we get only 4 Fridays, but with 22 working days. Even if you count all days of a month as working days, you will get 5 weeks / 30 days compared to 4 weeks / 31 days, right?

And all Months show either 4 or 5 Fridays, that means you divide your monthly sales either by 4 or 5, that is 25% difference, not taking into account that most month show 21 / 22 working days, which is a much smaller variation.

This will of course also affect your comparison of average weekly Sales.

So you might want to consider something like:

LOAD only(Month) as Month

,NetWorkDays(Month,MonthEnd(Month)) as NumWorkDays

,NetWorkDays(Month,MonthEnd(Month)) / 5 as NumWorkWeeks

Resident Calendar group by Month;

And use sum(NumWorkWeeks) to divide your count(distinct SaleId).

If you work all days in a month, it could look like:

LOAD only(Month) as Month

,day(Monthend(Month)) as NumWorkDays

,day(Monthend(Month)) / 7 as NumWorkWeeks

Resident Calendar group by Month;