Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Weekly Average Problem

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)

4 Replies
swuehl
MVP
MVP

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?

Not applicable
Author

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

swuehl
MVP
MVP

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

swuehl
MVP
MVP

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;