# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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.

Tags (2)
1 Solution

Accepted Solutions
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
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?

Highlighted
Valued Contributor II

## Re: Date greater than week end date?

try

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

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.

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

Valued Contributor II

## Re: Date greater than week end date?

New Contributor III

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

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

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)