Show Top 10 expenses rolling over current week, previous week, this month

[qlikwiz123]

I have Name and Total Expense and Date Submitted columns.

I need to show the following :

Top 10 People with most expense this week

Top 10 People with most expense previous week

Top 10 People with most expense this month.

How do I create this rolling period vs Top 10 people who spent the most?

[edwin]

try this

[qlikwiz123]

Hi @edwin ,

Thank you. What is WeekIndex here? I see all kinds of numbers without any order in both the years 2020 and 2021.

[edwin]

weekindex is my preferred way of indexing weeks (current week is 0, prior is 1).  you will appreciate it more when you allow users to select a date (the current week will be only(WeekIndex) prior week is +1 of that and so on.  its a lot easier that creating a complex expression to determine wht week you are in and what the prior weeks are.

what numbers are you referring to?  if you mean the charts, look at the expression and youll understand what its trying to do.  just remember week=0 is current week, week=1 is prior

[edwin]

the same solution applies to months, i also create month indices pretty much the same way

[qlikwiz123]

Thank you. I see a different order here

Current Week (week of 3/7/2021) shows as 5 while the week prior to it shows 10.

[edwin · Accepted solution]

my bad

[qlikwiz123]

Thank you 🙂

[edwin]

yw

[edwin]

one tip re rank

when you have values that are the same qlikview will give them the same rank and rightly so.  however, when you are reporting top n, you can get top n+ (1,2,3-7, 3-7, 3-7,3-7, 3-7)  sometimes this is acceptable
if this is not acceptable, you can bias your values based on what your business rules are.  for example, assign a fraction to each Customer (assuming you have 1000 customers), add .0999 to your first customer, .0998 to next and so on.  just add this bias to your values ensuring you take set analysis into consideration.  then you will not have multiple customers in the same rank.  obviously, if you dont know how many customers you are pulling in, you have to determine the count in your script.