Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a requirement on Week Over Week calculation based on Date Range Picker in Qlik sense and below is the scenario.
Date Range Picker with From and To Date:
If i select the From Date as 06-Dec-2019 and To Date as 20-Dec-2019 and my calculation is below,
vCurrentWeeks- 06-Dec-2019 to 20-Dec-2019 (Current 3 Weeks)
vPreviousWeeks- 15-Nov-2019 to 29-Nov-2019 (Previous 3 Weeks)
($(vCurrentWeeks)-$(vPreviousWeeks))/$(vPreviousWeeks)
Similarly expression should change dynamically based on the date range picker.
I will post the sample data if needed. Kindly help me on this.
Best Regards,
Siva
try below
Considering you have FromDate, ToDate as a different fields in Data from which you are picking the Date range.
Create variable in front end
vWeekOffset =(weekend(max(ToDate),0,4)-weekstart(max(FromDate),0,4))/7
vCurrentWeekStart = date(weekstart(max(FromDate),0,4))
vCurrentWeekEnd = date(weekend(max(ToDate),0,4))
vPreviousWeekStart = date(weekstart(max(FromDate),-$(vWeekOffset),4))
vPreviousWeekEnd = date(max(FromDate),0,4)-7)
If you are using single Date field as a date range picker then create variables as follows
vWeekOffset =(weekend(max(Date),0,4)-weekstart(min(Date),0,4))/7
vCurrentWeekStart = date(weekstart(min(Date),0,4))
vCurrentWeekEnd = date(weekend(max(Date),0,4))
vPreviousWeekStart = date(weekstart(min(Date),-$(vWeekOffset),4))
vPreviousWeekEnd = date(min(Date),0,4)-7)
Now you can use the expression
(Sum({<Date = {">=$(vCurrentWeekStart)<=$(vCurrentWeekEnd)"}>}Sales)
-
Sum({<Date = {">=$(vPreviousWeekStart)<=$(vPreviousWeekEnd)"}>}Sales))
/
Sum({<Date = {">=$(vPreviousWeekStart)<=$(vPreviousWeekEnd)"}>}Sales)
Let's say, You have measure like Sum(Sales) and the expression would be
((Sum({<Week={">=$(=Max(Week-3))<=$(=Max(Week))"}>} Sales)/Sum({<Week={">=$(=Max(Week-6))<=$(=Max(Week-3))"}>} Sales))/Sum({<Week={">=$(=Max(Week-6))<=$(=Max(Week-3))"}>} Sales))
how current and previous 3 weeks calculated based on Date?
Hi Anil,
Thanks much for your reply.
As I could see in your expression that you have hard coded the value as -6 and -3 to calculate the previous weeks.
But in my case, it should work dynamically based on the date range picker.
Below is the one example I have provided,
If i select the From Date as 06-Dec-2019 and To Date as 20-Dec-2019 and my calculation is below,
vCurrentWeeks- 06-Dec-2019 to 20-Dec-2019 (Current 3 Weeks)
vPreviousWeeks- 15-Nov-2019 to 29-Nov-2019 (Previous 3 Weeks)
($(vCurrentWeeks)-$(vPreviousWeeks))/$(vPreviousWeeks)
Similarly expression should work dynamically based on the date range picker as below.
If i select the From Date as 01-Nov-2019 and To Date as 29-Nov-2019 and my calculation should be,
vCurrentWeeks- 01-Nov-2019 to 29-Nov-2019 (Current 5 Weeks)
vPreviousWeeks- 27-Sep-2019 to 25-Oct-2019 (Previous 5 Weeks)
($(vCurrentWeeks)-$(vPreviousWeeks))/$(vPreviousWeeks)
Similarly it should work if i select any other date range.
Best Regards,
Siva
Yes. Current and Previous weeks are calculated based on Date field.
are those week start dates? How do you identify ranges?
Hi ,
Identifying date range based on the date range picker (custom date range filter)
My Date field has all the Fridays as below.
[Date]
20-Dec-2019
13-Dec-2019
06-Dec-2019
29-Nov-2019
22-Nov-2019
15-Nov-2019
08-Nov-2019
an so on ..
try below
Considering you have FromDate, ToDate as a different fields in Data from which you are picking the Date range.
Create variable in front end
vWeekOffset =(weekend(max(ToDate),0,4)-weekstart(max(FromDate),0,4))/7
vCurrentWeekStart = date(weekstart(max(FromDate),0,4))
vCurrentWeekEnd = date(weekend(max(ToDate),0,4))
vPreviousWeekStart = date(weekstart(max(FromDate),-$(vWeekOffset),4))
vPreviousWeekEnd = date(max(FromDate),0,4)-7)
If you are using single Date field as a date range picker then create variables as follows
vWeekOffset =(weekend(max(Date),0,4)-weekstart(min(Date),0,4))/7
vCurrentWeekStart = date(weekstart(min(Date),0,4))
vCurrentWeekEnd = date(weekend(max(Date),0,4))
vPreviousWeekStart = date(weekstart(min(Date),-$(vWeekOffset),4))
vPreviousWeekEnd = date(min(Date),0,4)-7)
Now you can use the expression
(Sum({<Date = {">=$(vCurrentWeekStart)<=$(vCurrentWeekEnd)"}>}Sales)
-
Sum({<Date = {">=$(vPreviousWeekStart)<=$(vPreviousWeekEnd)"}>}Sales))
/
Sum({<Date = {">=$(vPreviousWeekStart)<=$(vPreviousWeekEnd)"}>}Sales)
I tried the below logic and it is working as expected.
Thank you so much.
Best Regards,
Siva