Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Khan_Mohammed
Honored Contributor II

Set Analysis

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/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.

Tags (2)
1 Solution

Accepted Solutions

Re: Set Analysis

How about this:

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

View solution in original post

9 Replies
MVP
MVP

Re: Set Analysis

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

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

MVP & Luminary
MVP & Luminary

Re: Set Analysis

StartTime               EndTime          Status

11/27/2016          11/28/2016           C

On which day should this record be counted?

  • 27th?
  • 28th?
  • both?

talk is cheap, supply exceeds demand
Khan_Mohammed
Honored Contributor II

Re: Set Analysis

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.

Khan_Mohammed
Honored Contributor II

Re: Set Analysis

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

Re: Set Analysis

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)

Khan_Mohammed
Honored Contributor II

Re: Set Analysis

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.

Khan_Mohammed
Honored Contributor II

Re: Set Analysis

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

Re: Set Analysis

How about this:

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

View solution in original post

Khan_Mohammed
Honored Contributor II

Re: Set Analysis

It worked, thanks!!!