How to Display Latest Weeks Volume by Default

[aftabn10]

Hi, I am looking for some help in displaying a set of numbers on my dashboard but I need to display the latest week whenever the dashboard is open but also allow the user to change the week that they are looking at through the filters. 

My data is the following:

latest_week_rank | week_date | completed_orders

1 | 31/01/2020 | 3500
2 | 24/01/2020 | 6450
3 | 17/01/2020 | 6050
4 | 10/01/2020 | 6110
5 | 03/01/2020 | 4000
6 | 27/12/2019 | 3500
7 | 20/12/2019 | 7500
8 | 13/12/2019 | 7450
9 | 06/12/2019 | 7540
10 | 29/11/2019 | 6900
11 | 22/11/2019 | 7100
12 | 15/11/2019 | 7400
13 | 08/11/2019 | 7550

I am going to be using a Multi KPI Extension where I will display the volume of 3500 for the latest weeks volume in my data and then have a second measure to then display a % value to show if the volume is higher then previous week or lower.

so a formula: (3500 / 6450) giving me a % of 45.74% down 

The tricky bit is how to do the expression/variable to show the default of the latest week but also having the ability to filter and pick another week which would then change the previous week if the selection of the week_date is changed.

I would really appreciate if somebody could advise on how I could tackle this issue to display my data on my dashboard as I am fairly new to Qlik so just trying to get my head around how everything works.

Thanks in advance.

[Kashyap_R]

Hi

for Current week

Sum({<Date ={">=$(=Weekstart(max(Date)))<=$(=Weekend(max(Date)))"}>}Value)

%

Sum({<Date ={">=$(=Weekstart(max(Date)))<=$(=Weekend(max(Date)))"}>}Value)

/

Sum({<OrderDate ={">=$(=Weekstart(max(OrderDate),-1))<=$(=Weekend(max(OrderDate),-1))"},Week=>}Quantity)

Color and arrow

give the expression and set the color and Arrow

Sum({<OrderDate ={">=$(=Weekstart(max(OrderDate),-1))<=$(=Weekend(max(OrderDate),-1))"},Week=>}Quantity)

Hope this helps

Thanks

[aftabn10]

Thanks Kashyap, will try that and let you know how I get on.

[aftabn10]

Hi Kashyap, quick question, where you have outlined "for Current week" am I putting this directly as an Expression or creating a variable?

Also i'm guessing the % for the previous week volumes against the current week is what you are dividing right?

[Kashyap_R]

Hi Afthab

1. It is the direct expression

2. Current mOnth / Previous

Thanks

[aftabn10]

Thank you, got it working, one thing if I could change, my dates are based Sat to Fri and would like to use the latest_week_rank as Week_Start and Week_End is using the dates as Mon to Sun. Is it possible to change the code?

[Kashyap_R]

Hi Aftab

In Data load editor Main Section change the below variable

SET FirstWeekDay  as  0

Thanks

[aftabn10]

Hi Kashyap, apologies. I have wrote the following but this gives me a total for every week, 81050 in my KPI box rather than the volume for the current week only.

Sum({<Date ={">=$(=Weekstart(max(week_date)))<=$(=Weekend(max(week_date)))"}>}completed_orders)

Have I gone wrong anywhere?

When I filter on the week_date for the current week or any other week then this works exactly like how I want, just need this volume working and all done..

Thanks btw for all your help.

[Kashyap_R · Accepted solution]

Sum({<week_date={">=$(=Weekstart(max(week_date)))<=$(=Weekend(max(week_date)))"}>}completed_orders)

[aftabn10]

kashyap, you are a star! that works exactly how I wanted, thank you so much.