Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
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.
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,