topic Re: Set Analysis Required in QlikView

Set Analysis Required

Anonymous — Mon, 29 Sep 2014 08:30:59 GMT

Hello All,

I have already created a discussion for same but have not got proper syntax solution:

I have 3 fields(say):

INTime, ShiftStartTime, AttendanceStatus.

I have to use set analysis with the condition:

if(INTime>ShiftStartTime and AttendanceStatus='P','POW','MIS' then count(distinct(date))

IS it required any conversion for Time say NUM() to compare??

Please suggest.

Please find the sample attachment....

I have to generate a report of Emp with how many times he was late

Regards,

Balraj

Re: Set Analysis Required

jonathandienst — Mon, 29 Sep 2014 08:36:51 GMT

If INTime and ShiftStartTime are both timestamp or date and time fields, then no conversion is required:

Count({<AttendanceStatus={'P','POW','MIS'}>} DISTINCT If(INTime > ShiftStartTime, date))

HTH

Jonathan

Re: Set Analysis Required

manideep78 — Mon, 29 Sep 2014 08:40:06 GMT

Hi,

Try like this.

Count({<INTime={">shiftStartTime"},AttendanceStatus={'P','POW','MIS'}>}DISTINCT date)

Make you sure you have appropriate date formats in all the date fields.

hope this helps.

Regards,

Manideep

Re: Set Analysis Required

Anonymous — Mon, 29 Sep 2014 08:44:08 GMT

Your syntax is working similar to :

Count({<_AttendanceStatus={'P','POW','MIS'}>} distinct(Date))

which is not helping in validation..,..

Re: Set Analysis Required

Anonymous — Mon, 29 Sep 2014 08:46:13 GMT

Manideep,

Its returning 0.

I have checked twice format is same....

Re: Set Analysis Required

ashfaq_haseeb — Mon, 29 Sep 2014 08:49:53 GMT

Hi,

Can you post sample application?

Regards

ASHFAQ

Re: Set Analysis Required

manideep78 — Mon, 29 Sep 2014 08:51:11 GMT

Can you please post your app with sample data.

Re: Set Analysis Required

sagarkharpude — Mon, 29 Sep 2014 08:52:17 GMT

Try

Count({<INTime={">$(shiftStartTime)"},AttendacneStatus={'P','POW','MIS'}>}DISTINCT date)

Re: Set Analysis Required

jonathandienst — Mon, 29 Sep 2014 12:47:02 GMT

Then INTime and ShiftStartTime are not directly comparable timestamp fields. I suggest that you post a sample as others have suggested if you want help in diagnosing the problem.

Re: Set Analysis Required

Mon, 29 Sep 2014 13:51:26 GMT

This is how I've done it and it works correctly with the sample data you posted:

I added a calculated field to the script:

if(INTIme > ShiftTime, 1, 0) as Test

this returns 1 if INTime is greater than ShiftTime and 0 when it's not

The expression you then want to use is:

Count({<Test={'1'}, AttendanceStatus={'P','POW','MIS'}>} ID)

I attach a test dashboard!

Thank you