Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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
];
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)
The source data is like this
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)
Hi, thank you very much, just what I needed.
Regards.
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
];
Hi, thank you very much, I will keep it in mind.