topic Re: Set Analysis with Dates in QlikView

Set Analysis with Dates

Mon, 23 Mar 2015 07:38:48 GMT

I want to show the data for a specific date. Can you help me with the set analysis syntax for that please?

(It't the CreatedDateTime field). I want to show it for Today() -1.

=Count({$ <Status={"ACTIVE"},Allocated_Ind={"Unallocated"}, CreatedDateTime_Incident={'=$(=Today()-1)'} >} DISTINCT IncidentNumber)

Re: Set Analysis with Dates

tresB — Mon, 23 Mar 2015 07:42:19 GMT

Try like:

=Count({$ <Status={"ACTIVE"},Allocated_Ind={"Unallocated"}, CreatedDateTime_Incident={'=$(=Date(Today()-1))'} >} DISTINCT IncidentNumber)

Re: Set Analysis with Dates

PrashantSangle — Mon, 23 Mar 2015 07:42:58 GMT

Hi,

Try to match format of CreatedDateTime_Incident with Today()-1

Try like

=Count({$ <Status={"ACTIVE"},Allocated_Ind={"Unallocated"}, CreatedDateTime_Incident={"=$(=Date(Today()-1))"} >} DISTINCT IncidentNumber)

Regards

Re: Set Analysis with Dates

PrashantSangle — Mon, 23 Mar 2015 07:47:34 GMT

Hi,

When you did Today()-1, then date format is modified to num

Regards,

Re: Set Analysis with Dates

Mon, 23 Mar 2015 07:57:44 GMT

Thanks guys.

I'll give it a go quick go....

Re: Set Analysis with Dates

Mon, 23 Mar 2015 08:11:23 GMT

Hi,

My suggestion is if u calling any Date in Expression use number Format for that particular date that will give proper output instead of date Format

Thanks

Manju

Re: Set Analysis with Dates

Mon, 23 Mar 2015 08:12:42 GMT

Could I maybe doing something else wrong? It's not working. This is the current expression :

=Count({$ <Status={"ACTIVE"},Allocated_Ind={"Unallocated"}, CreatedDateTime_Incident={'=$(=Date(Today(1)-1))'} >} DISTINCT IncidentNumber).

Im using a LED guage....

Re: Set Analysis with Dates

Mon, 23 Mar 2015 08:18:34 GMT

Try this

=Count({$ <Status={"ACTIVE"},Allocated_Ind={"Unallocated"}, CreatedDateTime_Incident={'=$(num(Date(Today())-1))'} >} DISTINCT IncidentNumber).

Re: Set Analysis with Dates

tresB — Mon, 23 Mar 2015 08:23:10 GMT

Could be something specific to your app. Try to share a sample here to investigate.

Re: Set Analysis with Dates

Mon, 23 Mar 2015 08:25:00 GMT

Still not working. I'm getting a 0. (even though there are data for Today() -1.

Re: Set Analysis with Dates

Mon, 23 Mar 2015 08:51:39 GMT

How do I attach the qvw ?

Re: Set Analysis with Dates

tresB — Mon, 23 Mar 2015 08:54:24 GMT

reply->Use advanced editor(top right)->attach(bottom right)

Re: Set Analysis with Dates

Mon, 23 Mar 2015 08:59:54 GMT

I've reduced the data.

Check the Unallocated Sheet. Also consider : I hvae two Date fields I can use. : CreatedDateTime_Incident and CreatedDateTime_Proces_DayName. One is a date and the other numeric.

Re: Set Analysis with Dates

Mon, 23 Mar 2015 09:02:48 GMT

Here Am Attaching QVWJust go through That u will find the exact ans which one you are looking for

Just Change the variable expression according to u

Thanks

Manju

Re: Set Analysis with Dates

PrashantSangle — Mon, 23 Mar 2015 09:05:36 GMT

Hi,

I am using personal edition.

Just tell me

What is date format of CreatedDateTime_Incident.

Regards

Re: Set Analysis with Dates

Mon, 23 Mar 2015 09:12:00 GMT

Set a Variable for this expression

vtest'=(MAX(DATE1))-1'

'and call that variable in to expression

=sum({<DATE1={'$(VTEST)'}>}sales)

Re: Set Analysis with Dates

tresB — Mon, 23 Mar 2015 09:22:10 GMT

Your date field CreatedDateTime_Incident is a floating point numer field. If you need to compare that in the set analysis, better create a date filed in the script remving decimal part like:

Load

Date(Floor(SourceDate)) as CreatedDateTime_Incident

Then the written above should work.

Re: Set Analysis with Dates

Anonymous — Mon, 23 Mar 2015 09:40:34 GMT

Hi Bradley,

I checked your attached qvw for incident management

the issue you are facing is because CreatedDateTime_Incident is in Date time format

hence the date(today()-1) or anything light this will not match to it hence set analysis is giving 0

you have 2 options

1. simply add an additional column to your date truncating down the timestamp part of CreatedDateTime_Incident

you can name that as CreatedDate_Incident

2. frame you expression in greater than equal to start of the date of ( today()-1) i mean 00:00:01 hrs

and less than equal to end of the date of (today()-1) 23:59:59

Regards,

Bhagirath