Week Over Week calculation based on Date Range Picker in Qlik sense

[IAMSIVA]

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

[Anil_Babu_Samineni]

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))

[Kushal_Chawda]

how current and previous 3 weeks calculated based on Date?

[IAMSIVA]

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

[IAMSIVA]

Yes. Current and Previous weeks are calculated based on Date field.

[Kushal_Chawda]

are those week start dates? How do you identify ranges?

[IAMSIVA]

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 ..

[Kushal_Chawda · Accepted solution]

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)

[IAMSIVA]

I tried the below logic and it is working as expected.

Thank you so much.

Best Regards,

Siva