Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

maelafifi
New Contributor III

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.


1 Solution

Accepted Solutions
maelafifi
New Contributor III

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

10 Replies
arvind654
Honored Contributor II

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?

atkinsow
Valued Contributor II

Re: Date greater than week end date?

try


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

maelafifi
New Contributor III

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.

maelafifi
New Contributor III

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

atkinsow
Valued Contributor II

Re: Date greater than week end date?

diego_a_barboza
New Contributor III

Re: Date greater than week end date?

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

maelafifi
New Contributor III

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

maelafifi
New Contributor III

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?

This should work

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


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