topic Help needed for Time calculation in QlikView

Help needed for Time calculation

Anonymous — Wed, 15 Sep 2010 10:42:57 GMT

Hi QV-users.

Referring to my attached Excel file I have to make some calculations. I have made a novice solution in Excel but the file turns out to be so large that my computer runs out of memory when it calculate all rows.

Hopefully you can help me with a script to solve my problems:

1: Time spent in each line (EndDate-StartDate). If EndDate is empty then SysDate-StartDate. So I can sum time spent in each department. If the Status is BER or HBER then the time spent should be set to 0 (zero) from the StartDate with one of those Status and the following rows, e.g. in Order1_SubNo1 the time should be calculatede for StatusRowNo 1 and 2 but row 3 and 4 should be set to 0 (zero).

2: A complete view to see if an order is OK in all status rows, e.g. Order2_SubNo1 is the only order with OK in all status rows. If it's OK then the field value should be "OK" else "Fail".

3: Time spent from StartDate in StatusRowNo 1 to the StartDate where HBER or BER is shown as status for the first time. If they are not shown then the StartDate in StatusRowNo 1 to the EndDate in the last StatusRowNo or if not exist then to SysDate. This should be done for all OrderNo and SubNo.

4: A view to see which department fails an order, e.g. if department D fails in Order2_SubNo2 then this department should be "credited" as FailDepartment for all OrderNo_SubNo. It doesn't matter if another department fails later on, e.g. department A in Order2_SubNo2, then the FailDepartment is still department D.

Results should be:

Time per order:
OrderNo1_SubNo1 = 26 days
OrderNo2_SubNo1 = 56 days
OrderNo2_SubNo2 = 14 days

Time per department:
A = 44
B = 0
C = 52
D = 0

Time per FailDepartment:
A = 0
B = 26
C = 0
D = 14

Best regards,

Johnni

Help needed for Time calculation

renjithpl — Thu, 16 Sep 2010 13:01:08 GMT

Hi Johnni,

As per my understanding of your questions, i have attached a sample qv file. Just let me know what else you required. When Status BER or HBER i made it Zero, but i dont understand, in your excel file attached, some of the status OK have Zeros, so just confirm it.

Thanks

Ren

Help needed for Time calculation

renjithpl — Thu, 16 Sep 2010 13:15:40 GMT

Johni,

As per my understanding of your questions, i have attached a sample qv file.

Help needed for Time calculation

Anonymous — Thu, 16 Sep 2010 13:15:50 GMT

Hi Ren,

thanks for your help so far. Unfortunately I can't see your qvd-attachment in this post so please check whether you remembered to attach it or not.

Regarding my Excel file the time calculation must be zero from the day HBER or BER occur for the first time and for the following StatusRowNo.

Hopefully you can figure it out.

Johnni

Help needed for Time calculation

Anonymous — Thu, 16 Sep 2010 13:17:37 GMT

Now I can see your Calculation.qvw in the new post.

Thank you.

I will take at look at it and post back soon.

Johnni