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

How to compare average of last week with previous.

I have a master calendar with WEEK, DAY, MONTH etc.

How can I write set analysis expression?

I guess I need to use for one field smth like max(week) but it may return not the full current week?

Average I'm counting with this expression:

Sum(points)/Count(distinct table_id)

Here we can consider points as smth like sales. And table_id as one closed table in the restaurant,

Also how can I make this kind of comparison for different restaurants. I have them 3 in database with fields restaurant_id = 1,2,3 respectively.

Thank you for your time!

4 Replies
Not applicable
Author

Assuming you have Date in your Master Calendar, this would give you the recent full week:

IF(Num(Weekday(MAX(Date)))<4,Week(MAX(Date)-5),Week(MAX(Date)))

which looks at the most recent Date (which would also be your most recent week) and if that date is before a Friday, looks at the previous week.

hopefully that will help you

Not applicable
Author

Hi Andrew, thank you for your reply!

I have all 7 working days in the restaurant.

But I still not getting how to make a comparison

Not applicable
Author

am just learning Set Analysis myself, but I would imagine using:

IF(Num(Weekday(MAX(Date)))<7,Week(MAX(Date)-7),Week(MAX(Date)))

to give you last week

and

IF(Num(Weekday(MAX(Date)))<7,Week(MAX(Date)-14),Week(MAX(Date)-7))

to give you the week before that


Just be careful of your years - as the WEEK function does not give the Year.

mjm
Employee
Employee

Hello Evgeniy,

I would recommend you looking at this post which outlines ways to use set analysis properly to achieve comparisons against prior months:

QlikView App: Set Analysis - Prior Period Comparison

If this response is helpful, please mark this as helpful and correct.

Thanks,