Date greater than week end date?

[Anonymous]

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.

[MK9885]

Date greater than week?

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

[Anonymous]

try

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

[Anonymous]

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.

[Anonymous · Accepted solution]

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

[Anonymous]

See if this helps

Open cases as of date? | Qlik Community

[diego_a_barboza]

//What about this?

//-- Load script:

//-- Replace this inline with your datasource

Escalations_Temp:

LOAD  * INLINE [

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:

LOAD DATE#(OPEN_DATE_1,'DD/MM/YYYY') AS OPEN_DATE,

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

ESCALATION_NUMBER

RESIDENT Escalations_Temp;

DROP TABLE Escalations_Temp;

Intervals:

Load WEEK_NUMBER,

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)

Load distinct OPEN_DATE, CLOSED_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.

[Anonymous]

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

[Anonymous]

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!

[Anil_Babu_Samineni]

This should work

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

Note - Make sure, about your date format of 2 fields.