topic Re: Set Analysis help for WeekEnd expression -1week, -2week & -3Week in QlikView

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

Wed, 13 Apr 2016 11:47:40 GMT

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

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

sunny_talwar — Wed, 13 Apr 2016 15:50:53 GMT

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

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

Thu, 14 Apr 2016 08:02:10 GMT

Perfect