Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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...
Date greater than week?
Shouldn't it be Close date greater than Open date? Why are you adding week field?
try
=sum(if (CLOSE_DATE >OPEN_WEEK,1,0))
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.
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...
See if this helps
//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.
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).
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!
This should work
Count({<OPEN_DATE = {"=CLOSED_DATE > OPEN_DATE"}>} OPEN_DATE)
Note - Make sure, about your date format of 2 fields.