topic How to Display Latest Weeks Volume by Default in App Development

How to Display Latest Weeks Volume by Default

aftabn10 — Mon, 20 Dec 2021 21:38:40 GMT

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.

Re: How to Display Latest Weeks Volume by Default

Kashyap_R — Wed, 12 Feb 2020 09:56:16 GMT

for Current week

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

Re: How to Display Latest Weeks Volume by Default

aftabn10 — Wed, 12 Feb 2020 14:21:18 GMT

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

Re: How to Display Latest Weeks Volume by Default

aftabn10 — Wed, 12 Feb 2020 14:49:46 GMT

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?

Re: How to Display Latest Weeks Volume by Default

Kashyap_R — Wed, 12 Feb 2020 14:59:44 GMT

Hi Afthab

1. It is the direct expression

2. Current mOnth / Previous

Thanks

Re: How to Display Latest Weeks Volume by Default

aftabn10 — Wed, 12 Feb 2020 15:05:49 GMT

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?

Re: How to Display Latest Weeks Volume by Default

Kashyap_R — Wed, 12 Feb 2020 15:12:47 GMT

Hi Aftab

In Data load editor Main Section change the below variable

SET FirstWeekDay as 0

Thanks

Re: How to Display Latest Weeks Volume by Default

aftabn10 — Wed, 12 Feb 2020 15:23:36 GMT

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.

Re: How to Display Latest Weeks Volume by Default

Kashyap_R — Wed, 12 Feb 2020 15:25:54 GMT

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

Re: How to Display Latest Weeks Volume by Default

aftabn10 — Wed, 12 Feb 2020 15:34:02 GMT

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