Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Rolling 13 weeks and 26 weeks calculation based on week ending date

Week End date is Saturday instead of Sunday.

I have Week Ending date as Saturday and need to calculate the expression based on rolling 13,26 weeks.
But,in qlikview when i write expression =WeekEnd(max([Week Ending Date])) it is taking Sunday as Weekending date which is in correct and Saturday is Week ending date in my case.

It should display result based on 3/7/2020 ,3/14/2020 ,not 03/08 not 03/15. 

InkedWeek ending date snip_LI.jpg

 

Here is the exp = num(sum({<ID-={012}, [Week Ending Date]={">=$(=(WeekEnd(max([Week Ending Date]), -13)))<=$(=WeekEnd(max([Week Ending Date])))"}>}SLS*$(vCurrency))+sum({<ID={014}, [Week Ending Date]={">=$(=(WeekEnd(max([Week Ending Date]), -13)))<=$(=WeekEnd(max([Week Ending Date])))"}>}[Sold Units]*SRP*$(vCurrency))/1000, '$#,##0.0')

1 Solution

Accepted Solutions
Highlighted

Re: Rolling 13 weeks and 26 weeks calculation based on week ending date

In a text box with =WeekEnd('07/03/2020')  -in DD/M/YYYY format- it will return 8/3/2020 with the default SET FirstWeekDay=0;

If you use SET FirstWeekDay=6; it will return 7/3/2020

View solution in original post

3 Replies
Highlighted

Re: Rolling 13 weeks and 26 weeks calculation based on week ending date

Hi, there is a script variable called FirstweekDay that defines when a week starts (and ends), it's at the beggining of the script, try with:

SET FirstWeekDay=6;

Highlighted
Contributor II
Contributor II

Re: Rolling 13 weeks and 26 weeks calculation based on week ending date

I have 7 days in a week  and week start is Sunday and weekend is Saturday .

Highlighted

Re: Rolling 13 weeks and 26 weeks calculation based on week ending date

In a text box with =WeekEnd('07/03/2020')  -in DD/M/YYYY format- it will return 8/3/2020 with the default SET FirstWeekDay=0;

If you use SET FirstWeekDay=6; it will return 7/3/2020

View solution in original post