10 Replies Latest reply: Nov 6, 2017 8:55 AM by Diego Barboza

# Date greater than week end date?

Hi all,

I am having difficult getting a query like this to run, and I could really use some help. What I have is:

"OPEN_DATE",

"CLOSED_DATE",

WeekEnd("OPEN_DATE") AS OPEN_WEEK

What I am trying to get is the count of all OPEN_DATE where the CLOSE_DATE is greater than the OPEN_WEEK. I thought this would be as simple as:

count(CLOSE_DATE > OPEN_WEEK)

But I was sorely mistaken. I've looked into other queries through the forums, but I haven't had much luck at all.

Any help with this query would be much appreciated.

• ###### Re: Date greater than week end date?

Date greater than week?

Shouldn't it be Close date greater than Open date? Why are you adding week field?

• ###### Re: Date greater than week end date?

What I am trying to do is count, within a given week, how many open escalations were received that did not close within that same week. If it closed in the same week, it should be ignored.

• ###### Re: Date greater than week end date?

try

=sum(if (CLOSE_DATE >OPEN_WEEK,1,0))

• ###### Re: Date greater than week end date?

I think this is what I wanted to get out of this question, but having done so, I realized that it doesn't fully give me the result that I would like.

Any chance you would be able to help me in creating a running sum of all work weeks?

For example, what I am trying to do is:

week 1: 50 open escalations -- 50 total open

week 2: 25 new open escalations, and 15 of last weeks closed. 50 +20 - 15 = 55 total open

week 3: 10 new open escalations and 20 of the old opened are now close. 55 + 10 - 20 = 45 total open

And so on...

• ###### Re: Date greater than week end date?

The logic of that posts SEEMS like it should work, but I am not getting the proper output. Not sure if I am doing something wrong or if qliksense differs from qlik desktop. Thanks for the help, though!

• ###### Re: Date greater than week end date?

//-- Replace this inline with your datasource

Escalations_Temp:

OPEN_DATE_1,  CLOSED_DATE_1, ESCALATION_NUMBER

'02/10/2017', '02/11/2017', 564

'04/10/2017', '08/10/2017', 315

'08/10/2017', '20/10/2017', 444

'30/10/2017', '25/11/2017', 474

'10/10/2017', '05/11/2017', 465

'01/09/2017', '05/11/2017', 734];

//-- Check date format depending on your configuration

Escalations:

DATE#(CLOSED_DATE_1,'DD/MM/YYYY') AS CLOSED_DATE,

ESCALATION_NUMBER

RESIDENT Escalations_Temp;

DROP TABLE Escalations_Temp;

Intervals:

MONTH_CALENDAR,

YEAR_CALENDAR,

WEEK_START_DATE,

WEEK_END_DATE,

YEAREND(today()) as CURRENT_DATE

RESIDENT TempCalendar2;

//-- If you don't have a Calendar, check here: https://community.qlik.com/docs/DOC-15944

DROP TABLE TempCalendar2;

IntervalMatch:

IntervalMatch (WEEK_START_DATE)

resident Escalations;

// CHART OBJECT:

// Create a pivot table  with the followin dimension:

// -- WEEK_NUMBER

// And the following expression:

// count(distinct IF(CLOSED_DATE > WEEK_END_DATE , ESCALATION_NUMBER))

// Please, let me know if this helps.

• ###### Re: Date greater than week end date?

Unfortunately, this will not give me the output that I wanted. It addressed what I initially wanted, but after Wallo gave me a simpler solution, I quickly realized that I actually needed to add another condition to my statement (one that will count all open escalations BEFORE the current week, as well).

• ###### Re: Date greater than week end date?

Hello Mohamed,

Please check,because the object expression is counting all orders that are opened for matching week on calendar (since Closed date is greater than End week).

Just for you to verify, I am including the expression for closed orders.

// Accumulated Open Escalations

// count(distinct IF(CLOSED_DATE > WEEK_END_DATE , ESCALATION_NUMBER))

// Closed escalations

// count(distinct IF(CLOSED_DATE < WEEK_END_DATE  , ESCALATION_NUMBER))

• ###### Re: Date greater than week end date?

This should work

Count({<OPEN_DATE = {"=CLOSED_DATE > OPEN_DATE"}>} OPEN_DATE)