Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
commonqlik
Creator III
Creator III

Crazy reuirement in qlik life for time calculation?

Hi Folks,

i have a crazy requirement

background of this requirement is

i am capturing the employee information on daily basis and each task wise irrespective of shifts

it means example

emp a shift starts at 8 am

emp b shift starts at 1 pm

emp c shift starts at 6 pm

capturing the information irrespective of login time

here for emp a hour 1 is 8 am to 9 am

for emp b  is 1pm to 2 pm

for emp c  is  6pm to 7 pm

so hour 1 can be anything( any time)  ,

now here my requiremnet is i want see the hour1,hour2....,hour9 used tome for all employees

it means   emp a will start shift at 8 am ,

08:00 am to 08:30 am   meeting

08:30 am to  08:50 am  away

08:50 am to 09:00 am is mailbox checking

like wise for all employess we have information

now i want to see all employee usage in hour 1,hour2....hour 9.

tricky thing is here:

if employee exactly ends hour 1 end time then we can easy calculate it but for some of the employees tasks ends like this

08:00 am to 08:30 am   meeting

08:30 am to  08:50 am  away

08:50 am to 09:10 am is mailbox checking

from abobe data mailbox task is falling under   hour 1 and hour 2

so here we have to take 10 mins of time from mailbox and put it into hour 2. and 10 mins time to hour 1 ,

mail box task should be there in both hour 1 and hour 2

I am attaching sample data file to the link pls have a look and let me know ur thoughts

i have source columns like this

Team    -team name (database)

Region    -(india)

SPOC     -(emp name)

Date     -date

Start Time -starting time of each employee

End Time - ending time of employee   

Time Spent (mins) - time spent

Broad Category  - used for (like mails,meetings,away...)

attached   my work application .....

25 Replies
Anil_Babu_Samineni

Can you read this? Buckets

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
commonqlik
Creator III
Creator III
Author

Can u pls look into this , am not understanding

Anil_Babu_Samineni

Can we split as short question rather pages. Because, I am not en-count the issue where we start from. I meant say, please tell us expected result by providing the sample Excel

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
arvind_patil
Partner
Partner

Hi ,


Please tell us expected result by providing the sample Excel.


Thanks,

Arvind Patil

Crazy reuirement in qlik life for time calculation?

Anil_Babu_Samineni

May be this? I've seen in your excel from sheet2. I assume that is output you need

Capture.PNG

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
commonqlik
Creator III
Creator III
Author

Yes Exactly Anil

commonqlik
Creator III
Creator III
Author

I have attached xl file

pls see the sheet2 for out put

Anil_Babu_Samineni

Haha, May be you can close this thread by flag correct answer and Helpful

Note - We are always help you if you still trouble please free to ask till satisfied

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
commonqlik
Creator III
Creator III
Author

no anil ,

hour 1 ,hour 2 columns are calculated in xl level

which i want to do this calculation in qlikview level

load

only these columns from  from xl file ...  like

Temp:

LOAD Team as Empname,

     Region,

     SPOC as empname ,

   

     Date,

     [Start Time] as Startrime,

     [End Time] as Endtime,,

Broad Category as category

     [Time Band],

  

FROM

C:\Users\Harikrishna\Downloads\Test.xlsx

(ooxml, embedded labels, table is Sheet1);

from this information we have to calculate hour1,hour 2 ...hour 09 for each employee

time spent for each employee on hourly based....