topic Timestamp field between two other fields in QlikView

Timestamp field between two other fields

Anonymous — Sun, 24 Jul 2016 18:44:20 GMT

See attached. I'm thinking this is a formatting issue but I've lost enough time to figuring it out myself.

I'm trying to write the set analysis for Task_Opened_At is between Visit_FirstViewByDay and Visit_IncidentWindowEnd but nothing has worked for me. The results should be zero so I'm testing with Task_Opened_At <= Visit_FirstViewByDay and >= Visit_IncidentWindowEnd. This should count all the records but I'm getting no results.

Re: Timestamp field between two other fields

Frank_Hartmann — Sun, 24 Jul 2016 18:50:11 GMT

Try like:

1.Possibility:

Dimension:

=if(Task_Opened_At<= Visit_FirstViewByDay and Task_Opened_At>= Visit_IncidentWindowEnd and Visit_ScriptsRun>0 ,Visit_Date)

Expression:

=Count(DISTINCT Task_Number)

2.Possibility:

Dimension:

=Visit_Date

Expression:

count({<Task_Opened_At={">=$(=only(Visit_IncidentWindowEnd,'M/D/YYYY'))<=$(=only(Visit_FirstViewByDay,'M/D/YYYY'))"},Visit_ScriptsRun={'>0'}>}DISTINCT(Task_Number))

hope this helps

Re: Timestamp field between two other fields

sunny_talwar — Sun, 24 Jul 2016 21:52:45 GMT

How about this expression:

Count(DISTINCT {<Task_Number = {"=Task_Opened_At <= Visit_FirstViewByDay and Task_Opened_At >= Visit_IncidentWindowEnd"}>} Task_Number)

Re: Timestamp field between two other fields

PrashantSangle — Mon, 25 Jul 2016 07:06:20 GMT

Hi,

What about this expression,

Count(DISTINCT {<Task_Opened_At={"<=$(=max(Visit_FirstViewByDay))>= $(=min(Visit_IncidentWindowEnd))"}>}Task_Number)

Regards,

Re: Timestamp field between two other fields

Anonymous — Mon, 25 Jul 2016 14:03:31 GMT

I haven't tried this yet but I'm thinking this will cause my dates chart to show only the dates where a Task exists and I like to show zero values.
This one appeared to work correctly at first but as soon as I switched to <=Visit_IncidentWindowEnd and >=Visit_FirstViewByDay the data doesn't change. It should show zero results.

Re: Timestamp field between two other fields

Anonymous — Mon, 25 Jul 2016 14:04:01 GMT

No luck with any of the expressions.

Re: Timestamp field between two other fields

sunny_talwar — Mon, 25 Jul 2016 14:05:42 GMT

The image provided above is not what you are looking to get?

What is the expected output here?

Re: Timestamp field between two other fields

Anonymous — Mon, 25 Jul 2016 14:13:45 GMT

No because the formula isn't actually working. It looks like you used FLOOR() on all the time stamps because their times have been lost and this expression does not work in my original file.

Re: Timestamp field between two other fields

sunny_talwar — Mon, 25 Jul 2016 14:19:34 GMT

There is no Floor function used:

Count(DISTINCT {<Task_Number = {"=Task_Opened_At <= Visit_FirstViewByDay and Task_Opened_At >= Visit_IncidentWindowEnd"}>} Task_Number)

Have you tried opening the file I have attached with my initial response?

Re: Timestamp field between two other fields

Anonymous — Mon, 25 Jul 2016 14:25:51 GMT

I have. If you look at the data in the table box in the file you attached all the time stamps have lost their actual time and look like FLOOR() was used on them. When I refresh the data in the file you attached with the original data, this expression does not work.

Re: Timestamp field between two other fields

sunny_talwar — Mon, 25 Jul 2016 14:29:34 GMT

That is very strange because I have not done any manipulation in the script. I just used what you posted above and offered an expression. I will try to reload with the attached file to see what is the difference here

Re: Timestamp field between two other fields

sunny_talwar — Mon, 25 Jul 2016 14:37:01 GMT

Based on the two conditions you have provided:

1) =Task_Opened_At <= Visit_FirstViewByDay

2) =Task_Opened_At >= Visit_IncidentWindowEnd

All your Task Number been the 1st condition, but fail the second condition

Are these two conditions correct?

Re: Timestamp field between two other fields

Anonymous — Mon, 25 Jul 2016 14:41:47 GMT

I noted AND in my original post and I guess it should be an OR? The main thing I need to do is count task numbers when the task was opened outside the 6 hour window (Visit_FirstViewByDay to Visit_IncidentWindowEnd - IncidentWindowEnd is a simple DateAdd(hour,6,Visit_FirstViewByDay_ in my actual SQL SELECT).

Once I've done that I just need to duplicate the formula and count tasks that were opened in that 6 hour window.

Re: Timestamp field between two other fields

sunny_talwar — Mon, 25 Jul 2016 14:51:00 GMT

Can you re-explain the two condition with an example?

Re: Timestamp field between two other fields

Anonymous — Mon, 25 Jul 2016 14:59:20 GMT

I can and I've also updated the QVW and XLS to show better examples.

I'm trying to write two expressions that I will put into a bar chart with Visit_Date as the dimension. The bar chart will show a

Inside Visit Window: Distinct count of Task_Number where Task_Opened_At is between Visit_FirstViewByDay and Visit_IncidentWindowEnd
Outside Visit Window: Distinct count of Task_Number where Task_Opened_At is NOT between Visit_FirstViewByDay and Visit_IncidentWindowEnd

In the original post the data did not contain any Tasks opened between the two time stamps. I've since added test data for this example.

Re: Timestamp field between two other fields

Mon, 25 Jul 2016 15:23:53 GMT

Do you have to use set analysis? I think you can set up a flag when loading the script. Please see attached. I changed data in your xls for testing.

Re: Timestamp field between two other fields

Anonymous — Mon, 25 Jul 2016 15:35:44 GMT

I dont want to say I can't use set analysis but this load script is not my real load script. Visit information is coming from an SQL server and Task information is coming from a Sybase server. I'll look at your file shortly.

Re: Timestamp field between two other fields

Mon, 25 Jul 2016 15:42:41 GMT

You should be able to add the flag through residential load. Also date comparison calculation happens at load script, which, based on my reading, should give better performance than putting it in an UI expression.

Re: Timestamp field between two other fields

Anonymous — Mon, 25 Jul 2016 16:09:06 GMT

This is my first time working with data from two different sources so I'm not sure how I can combine the two to flag this. Have an example anywhere? Below is where the data comes from and their table names in my LOAD script.

Visits (SQL):

Visit_FirstViewByDay

Visit_IncidentWindowEnd

Tasks (Sybase):

Task_Number

Task_Opened_At

Re: Timestamp field between two other fields

Mon, 25 Jul 2016 17:14:18 GMT

Please see if this helps.