Timestamp field between two other fields

[Anonymous]

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.

[Frank_Hartmann]

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)

or

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

[sunny_talwar]

How about this expression:

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

[PrashantSangle]

Hi,

What about this expression,

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

Regards,

[Anonymous]

1. 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.
   
2. 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.

[Anonymous]

No luck with any of the expressions.

[sunny_talwar]

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

What is the expected output here?

[Anonymous]

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.

[sunny_talwar]

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?

[Anonymous]

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.