Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
PabloOrtiz
Partner - Creator
Partner - Creator

Two ranking in straight table

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.

2 Solutions

Accepted Solutions
Anonymous
Not applicable

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)

View solution in original post

bismart
Creator
Creator

Hi

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
];

 

 

View solution in original post

6 Replies
Anonymous
Not applicable

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)

 

PabloOrtiz
Partner - Creator
Partner - Creator
Author

The source data is like this

Anonymous
Not applicable

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)

PabloOrtiz
Partner - Creator
Partner - Creator
Author

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

Regards.

 

bismart
Creator
Creator

Hi

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
];

 

 

PabloOrtiz
Partner - Creator
Partner - Creator
Author

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