Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have one file with the (AppL_ID, App_Date, Event_TYpe, Evt_DTE) data , Second file has ( Decision Date):
APPL_ID app_date event_type EVT_DTE Decision Date(This date is on separate Sheet)
123 06/09/2013 entered 06/09/2013
123 06/09/2013 analysed 06/09/2013
123 06/09/2013 referral event 06/09/2013 19/09/2013
456 12/09/2013 entered 12/09/2013
456 12/09/2013 analysed 12/09/2013
456 12/09/2013 entered 12/09/2013
456 12/09/2013 analysed 24/09/2013
456 12/09/2013 referral event 24/09/2013 26/09/2013
789 19/09/2013 entered 19/09/2013
789 19/09/2013 referral event 19/09/2013
789 19/09/2013 analysed 19/09/2013
149 24/09/2013 entered 24/09/2013
149 24/09/2013 referral event 24/09/2013 28/09/2013
158 26/09/2013 entered 26/09/2013
158 26/09/2013 analysed 27/09/2013
158 26/09/2013 entered 27/09/2013
158 26/09/2013 analysed 27/09/2013
158 26/09/2013 referral event 27/09/2013
158 30/09/2013 entered 30/09/2013
158 30/09/2013 referral event 30/09/2013
158 30/08/2013 entered 30/08/2013
158 30/08/2013 analysed 04/09/2013
158 30/08/2013 referral event 04/09/2013 09/09/2013
Entered, Anlaysed & Referral Event could be multiple times. I need to calculate as follows:
1). Analysed (Last instance) Date - Entered (First Instance) Date
2). Referral Event (Last instance) Date - Analysed (Last Instance) Date
3). Decisioned Date - Referral Event (Last Instance) Date
After that I need to calculate Average working days for all 3 as above. Could you please tell how and which code should I use as I am newto Qlikview
Try the QVW attached. You should be able to open it.
What do you call 'Last Instance'? Last in time, or last in a row?
Look at the last four records for ID=158. The first one is the most recent referral event, but the last one (almost a month earlier) is the one you want to keep for calculations?
Peter
Hi Peter,
First is the earliest one and last is the latest. In 158 exp first referral event is on 04/09/2013 and the last is on 27/09/2013
So Last instance is last in time and first instance is first in time
Try using one or more of the following expressions in the script in conjunction with "group by":
firstsortedvalue()
FirstValue()
LastValue()
The Help section within QlikView provides a few usage examples.
Are you able to upload the data files?
Could you please tell step by step
Here is one approach (QVW attached):
Data:
LOAD APPL_ID,
app_date,
event_type,
EVT_DTE,
DecisionDate
FROM
[SampleData.xlsx]
(ooxml, embedded labels, table is Sheet1);
AnalysedLast:
LOAD
APPL_ID,
FirstValue(app_date) as AnalysedLastDate
resident Data
where event_type = 'analysed'
group by APPL_ID
order by app_date desc;
EnteredFirst:
LOAD
APPL_ID,
FirstValue(app_date) as EnteredFirstDate
resident Data
where event_type = 'entered'
group by APPL_ID
order by app_date;
... and so on. You can then use the new calculated dates in a table.
The disadvantage of this script approach is that selections (filters) in the dashboard don't affect the values.
I need to use Evt_DTE not APp_Date. Also is there any way to sort selection problem.
Not a problem. Simply replace app_date with Evt_DTE in my example.
I'll have a look at the selection challenge. It'll need to be on-the-fly rather than calculated once during script execution.
Thanks Kai