topic Re: Set Analysis in QlikView

Set Analysis

MK9885 — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I'm trying to write a set expression where I have Date and time filed

StartTime EndTime Status

11/28/2016 11/29//2016 C

11/27/2016 11/28/2016 C

11/28/2016 11/29/2016 P

I want to write an expression to count only Status C from today. Every morning I'll check for C for todays date and if it is yesterdays date it should show count of C as 0.

I've tried

=count({<STATUS={'C'},LastDay>} LOADSTATUS)

Last Day is my yesterday's date.

Or I should be using todays date also as filter? With above table I should get count of C for today as only 1 and not 3.

stalwar1‌

Thanks.

Re: Set Analysis

MK_QSL — Tue, 29 Nov 2016 15:54:04 GMT

I am assuming that you are comparing Today and Yesterday with EndTime

COUNT({<Status = {'C'}, EndTime = {'$(=Date(Today(),'MM/DD/YYYY'))'}>}Status)

Re: Set Analysis

Gysbert_Wassenaar — Tue, 29 Nov 2016 15:56:20 GMT

StartTime EndTime Status

11/27/2016 11/28/2016 C

On which day should this record be counted?

27th?
28th?
both?

Re: Set Analysis

MK9885 — Tue, 29 Nov 2016 16:00:02 GMT

For todays date

StartTime EndTime Status

11/28/2016 13:09:16 PM 11/28/2016 13:45:16 PM C

Actually Start & end time will be same but there will be time difference.

If it is for yesterdays date then Status count should be 0 and if it is 11/29/2016 then count should be 1.

Re: Set Analysis

MK9885 — Tue, 29 Nov 2016 16:00:29 GMT

I tried but exp gives error for MM/DD/YYYY field.

Re: Set Analysis

sunny_talwar — Tue, 29 Nov 2016 16:04:35 GMT

I think you see an error because of single quotes within single quotes, try this may be

Count({<Status = {'C'}, EndTime = {"$(=Date(Today(),'MM/DD/YYYY'))"}>}Status)

Re: Set Analysis

MK9885 — Tue, 29 Nov 2016 16:06:59 GMT

My start and end time also has time stamp

StartTime EndTime Status

11/28/2016 13:09:16 PM 11/28/2016 13:45:16 PM C

after using your expression and for testing purpose I changed the date to 11/29/2016 and the count still shows 0 but there is 1 record for today's date.

Re: Set Analysis

MK9885 — Tue, 29 Nov 2016 16:17:08 GMT

I'm facing this issue cus Date and Time are together. Or else the expression works fine.

Re: Set Analysis

sunny_talwar — Tue, 29 Nov 2016 16:19:08 GMT

How about this:

Count({<Status = {'C'}, EndTime = {"=Floor(EndTime) = Today()"}>}Status)

Re: Set Analysis

MK9885 — Tue, 29 Nov 2016 16:23:41 GMT

It worked, thanks!!!