topic Re: Two ranking in straight table in QlikView

Two ranking in straight table

PabloOrtiz — Wed, 25 Nov 2020 16:16:04 GMT

Hi all, I need help to solve a problem. I have a set of sales data, and I would need to calculate the sales ranking by product in the current week and the previous week in a straight table.

I can get the ranking of the current week with a RowNo (Total) but I can not calculate the ranking of the previous week simultaneously

Attached excel with the result to obtain.

Thanks to everyone.

Re: Two ranking in straight table

Anonymous — Tue, 04 Dec 2018 08:06:59 GMT

what is your source data?

if you have a field for week you Need to use set Analysis

rank(sum({<yourweekfield={'$(=yourweekfield-1)'}>}Sales) (with Dimension of your products)

Re: Two ranking in straight table

PabloOrtiz — Tue, 04 Dec 2018 08:21:33 GMT

The source data is like this

Re: Two ranking in straight table

Anonymous — Tue, 04 Dec 2018 08:41:00 GMT

you Need to define 4 expressions

As I do not know if you have a selection fiield for your week, I defined max(week) and max (Week-1). you may adapt it to your purposes. I cannot upload my little example

Dimension: Products

Expr1: =rank(sum({<Week={'$(=max(Week))'}>}Val))
Expr 2: =sum({<Week={'$(=max(Week))'}>}Val)
Expr 3: =rank(sum({<Week={'$(=max(Week)-1)'}>}Val))
Expr 4: =sum({<Week={'$(=max(Week)-1)'}>}Val)

Re: Two ranking in straight table

PabloOrtiz — Tue, 04 Dec 2018 08:45:49 GMT

Hi, thank you very much, just what I needed.

Regards.

Re: Two ranking in straight table

bismart — Tue, 04 Dec 2018 10:21:34 GMT

You're current solution doesn't cover movement from year to year.

Weeks can be tricky.

Use variable for max week as num(weekstart(yourdate)) and max week -1 as vMaxWeek-7

This will allow you check from week 1 in 2018 against last week in 2017 for example

Dave

Load
*,
year(date) as year,
Month(date) as month,
week(date) as week,
num(weekstart(date)) as weeknum;

LOAD * INLINE [
date, qty
15/12/2017, 1
24/12/2017, 2
29/12/2017, 3
04/01/2018, 4
13/01/2018, 5
24/01/2018, 6
];

Re: Two ranking in straight table

PabloOrtiz — Tue, 04 Dec 2018 10:40:40 GMT

Hi, thank you very much, I will keep it in mind.