topic Fix timeframe with set analysis in QlikView

Fix timeframe with set analysis

Thu, 17 Apr 2014 09:16:35 GMT

Hi,

With the following formula, I calculated in my Qlikview document how many minutes trucks that were supposed to unload, and didn't give a status update yet, were late:

=(Avg({<stp_status={[OPN>}now())-Avg({<stp_status={[OPN]}>}stp_schdtlatest))*1440

So stp_status OPN means they didnt give an update yet, and stp_schdtlatest is the latest time the truck was allowed to arrive.

I used to limit the timeframe in my script, so I would only get data from yesterday, today, and tomorrow.

(with: "Where stp_schdtlatest>getdate()-1 and stp_schdtlatest<getdate()+1 and stp_event='unload')

However, now I need to show how many minutes late the LOADING trucks are too. My dataset contains information with unloading and loading information from trucks. There are many different places for loading as well as for unloading. So it's not like I have just information from one site with incoming trucks. So now I cannot limit the timeframe in my script to trucks unloading from yesterday to tomorrow anymore, because then I'll filter out the trucks that will be loaded today and will only be unloaded 4 days from now.

So basically I have to limit the timeframe in my data now using set analysis, so it will only show data from yesterday, today, and tomorrow. Could anybody adjust the formula on top so it does this?

Thanks.

Niek

Re: Fix timeframe with set analysis

MK_QSL — Thu, 17 Apr 2014 09:19:26 GMT

Can you provide some little sample data?

Re: Re: Fix timeframe with set analysis

Thu, 17 Apr 2014 09:37:28 GMT

Hi Manish,

I have my file directly connected to my companies database, so I cannot send you the data as I use it. We also have a program to pull data from the database, you can find the output attached. Below you can see how the data looks in the transport planning software: in the upperhalf the planned loading date+time and unloading date+time, in the lowerhalf the actual loading date+time and unloading date+time. The 'A's, under Est in the lowerhalf, shows if it is still open (E) or closed (A).

Hopefully this helps.

Thanks,

Niek

Re: Fix timeframe with set analysis

MK_QSL — Thu, 17 Apr 2014 09:46:12 GMT

I can't find

stp_status & stp_schdtlatest here

Re: Fix timeframe with set analysis

Thu, 17 Apr 2014 09:52:04 GMT

Stp_status is not in the attached file, stp_schdtlatest is a combination of these two fields:

Sched Delivery Latest Date

Sched Delivery Latest Time

Like I said; this is the output of a program we have; it translates the database in an understandable format, with preselected fields. So it's only use is to give you some more understanding of the data.

Best regards,

Niek