Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Connect 2026! Turn data into bold moves, April 13 -15: Learn More!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis help for WeekEnd expression -1week, -2week & -3Week

HI

I have a pivot table which displays open Maintenance calls. I want 3 expressions across the top that show the quantity of open calls for the weekending for this week, last weeks week end and the week before lasts weekend.

I'm having no problem showing the total for a fiscal year:

=sum({1<[Calendar Fiscal Year]= {'2015-2016'},[Maintcon CallStatus]={'Open'}>}[Maintcon Count])

I tried using the WeekEnd() function like this and this shows the total that were opened in that week end period not the total that was still open/outstanding at that date:

=sum({1<[CalendarWeekEnd]= {'$(vWeekEnd)'}, [Maintcon CallStatus]={'Open'} > }[Maintcon Count])

          The variable  $(vWeekEnd)  is set to :=WeekEnd(Today())

I thought I could just stick a -7 after the Today()-7 but this does not work.

Thanks

Nick

1 Solution

Accepted Solutions
sunny_talwar

May be you need this:

This Week

=Sum({1<DateField = {"$(='>=' & Date(WeekStart(Today()), 'DateFieldFormatHere') & '<=' & Date(WeekEnd(Today()), 'DateFieldFormatHere'))"}, [Maintcon CallStatus]={'Open'}, CalendarWeekEnd> }[Maintcon Count])


Previous Week

=Sum({1<DateField = {"$(='>=' & Date(WeekStart(Today(), -1), 'DateFieldFormatHere') & '<=' & Date(WeekEnd(Today(), -1), 'DateFieldFormatHere'))"}, [Maintcon CallStatus]={'Open'}, CalendarWeekEnd> }[Maintcon Count])


Previous 2 Week

=Sum({1<DateField = {"$(='>=' & Date(WeekStart(Today(), -2), 'DateFieldFormatHere') & '<=' & Date(WeekEnd(Today(), -2), 'DateFieldFormatHere'))"}, [Maintcon CallStatus]={'Open'}, CalendarWeekEnd> }[Maintcon Count])

View solution in original post

2 Replies
sunny_talwar

May be you need this:

This Week

=Sum({1<DateField = {"$(='>=' & Date(WeekStart(Today()), 'DateFieldFormatHere') & '<=' & Date(WeekEnd(Today()), 'DateFieldFormatHere'))"}, [Maintcon CallStatus]={'Open'}, CalendarWeekEnd> }[Maintcon Count])


Previous Week

=Sum({1<DateField = {"$(='>=' & Date(WeekStart(Today(), -1), 'DateFieldFormatHere') & '<=' & Date(WeekEnd(Today(), -1), 'DateFieldFormatHere'))"}, [Maintcon CallStatus]={'Open'}, CalendarWeekEnd> }[Maintcon Count])


Previous 2 Week

=Sum({1<DateField = {"$(='>=' & Date(WeekStart(Today(), -2), 'DateFieldFormatHere') & '<=' & Date(WeekEnd(Today(), -2), 'DateFieldFormatHere'))"}, [Maintcon CallStatus]={'Open'}, CalendarWeekEnd> }[Maintcon Count])

Not applicable
Author

Perfect