Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team.
Can I please get your assistance on the below.
I have 3 columns(ID,OpenDate,ResolveDate) in my Qlik application as shared below. I would basically like to start counting on a day by day basis the AGE of the order_ids that are not resolved on the same day.
example:
2021/02/08 : Has 10 Open Calls but not all are resolved on the same day ( 6 are resolved on the same day). Hence on
2021/02/09: The AGE of all these 4 calls(not resolved) will be 1 ( 1 day old) , so AGE =1 will count 4 calls and on the
2021/02/10: Since we have 2 Calls resolved on this day from 2021/02/08 the AGE = 2 since its two days after the open_date.
2021/02/11 : Nothing since no calls were resolved on this day from 2021/02/08/
I am not sure how to go about this but I have tried using the Below() and Above() functions but have since not been able to get any luck in resolving the issue.
Thanks in advance.
Hi @Ksrinivasan.
Thanks for the response.
The solution you have provided is basically counting the number of calls/IDs resolved same day, 1 day after , 2 days after the initial open_date.
What I am looking to get is the count of IDs still open from the previous day(WO_AGE_1) and if the same call/ID is still not resolved in the next day we assign it lable 'WO_AGE_2'.
Instead of the using the 'closed date' the focus should be around the previous day of the open_date.
the assumption is that when we reload the data we dont know the 'closed date' of the call.
example:
The idea is that if a call/ID is open today and its resolve_dated is 3 days after, then on the days before the call is resolved we should label or included these in the count of WO_AGE_1 and then moved on to WO_AGE_2 the day before it was closed.
I hope this helps.
I wasn't able to work all the way through your problem as I have my own tasks. But this is as far as I've gotten.
Below is the code I've used and the explanation is beneath it.
DATA:
LOAD ID AS '%id_key',
Date(Date#(OpenString,'MM-DD-YYYY')) AS 'Open',
Date(Date#(CloseString,'MM-DD-YYYY')) AS 'Close',
If(OpenString = CloseString, 'Y','N') AS 'Same Day',
*
;
LOAD * Inline
[
'ID','OpenString','CloseString'
A, '01-01-2021', '01-01-2021'
G, '01-01-2021', '03-01-2021'
V, '02-01-2021', '07-01-2021'
]
;
DROP FIELDS OpenString, CloseString
;
CalJoinTable:
Load
%id_key,
DayStart(Floor(Num(Open + ((IterNo() - 1))))) AS '%fact_calendar_key'
RESIDENT DATA
While Open + (IterNo() - 1) <= Close
;
MasterCalendar:
LEFT KEEP(CalJoinTable)
LOAD
DayStart(Floor(Num(TempDate))) AS %fact_calendar_key,
Date(DayStart(TempDate)) AS CalDate,
;
//=== Generate a temp table of dates ===
LOAD
DATE(mindate + IterNo()) AS TempDate
WHILE mindate + IterNo() <= maxdate;
//=== Get min/max dates from Field ===/
LOAD
MIN(FieldValue('%fact_calendar_key', recno()))-1 as mindate,
MAX(FieldValue('%fact_calendar_key', recno())) as maxdate
AUTOGENERATE FieldValueCount('%fact_calendar_key');
EXIT Script
;
1. Create a Calendar Join table that contains the %id_key, along with the date of duration
2. Join this to a Master Calendar
3. Create a Pivot table with
Dimension = CalDate,
Column =Pick(AGGR(Count(DISTINCT ID), CalDate), 'AGE 01', 'AGE 02')
Measure = Count(DISTINCT ID)
This gives me a pivot table with something like this.
It's lacking the Same Day column that you've got, but I have a flag on my Data table that can be used to facilitate that.
hi,
kindly confirm data, formula and result in excel screenshot,
you can use this formula rest of your data and confirm the result,
then we will do it in qlik sense,
ksrinivasan
