Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
drohm002
Contributor II
Contributor II

Selecting last 2 instances of a date-time field for each day

Hello,

I have a field called BEGIN_SURGPROC_DT_TM with values like the ones listed below.  How can I create a formula that displays the last two instances for each day?  Thank you.  So of the values below, I would want the last 2 latest times for the day, which would be 1/25/2019 751AM and 1/25/2019 741AM.

drohm002_1-1595437957304.png

 

1 Solution

Accepted Solutions
benvatvandata
Partner - Creator II
Partner - Creator II

So like this?

benvatvandata_1-1595452819073.png

Do you have a regular Date field?

This solution is based off if you do... if you don't then you could just add a regular Date field to your table by adding Date(DateTime) as Date in your script... otherwise it might be a little trickier if you don't want to do that...

But so what I did was:

1) create a straight table chart

2) add DateTime field as a Dimension

3) add this expression:

  • =only({< DateTime = {"=aggr(rank(DateTime),Date,DateTime)<=2"} >} DateTime)

4) in presentation tab of the object, hide your DateTime Dimension column 

 

Let me know if that works for you.

 

Thanks,

Ben

View solution in original post

6 Replies
benvatvandata
Partner - Creator II
Partner - Creator II

Is this just going to be like a list box to select from? or are you going to use it in a chart? 

drohm002
Contributor II
Contributor II
Author

I want to display it in a table chart...so basically create a dimension that captures the last 2 instances for each day and lists them

drohm002
Contributor II
Contributor II
Author

I want to display it in a table chart...so basically create a dimension that captures the last 2 instances for each day and lists them

Kushal_Chawda

Probably you need to have Date field calculated BEGIN_SURGPROC_DT_TM  if you already have it then make use of it else need to create one

Data:

LOAD *,

           floor(BEGIN_SURGPROC_DT_TM) as BEGIN_SURGPROC_DT_TM_NUM

FROM Source

Then you can use below expression in straight table. 

=aggr(if(BEGIN_SURGPROC_DT_TM>= max(total <BEGIN_SURGPROC_DT_TM_NUM> BEGIN_SURGPROC_DT_TM,2),BEGIN_SURGPROC_DT_TM),BEGIN_SURGPROC_DT_TM,BEGIN_SURGPROC_DT_TM_NUM)

 

benvatvandata
Partner - Creator II
Partner - Creator II

So like this?

benvatvandata_1-1595452819073.png

Do you have a regular Date field?

This solution is based off if you do... if you don't then you could just add a regular Date field to your table by adding Date(DateTime) as Date in your script... otherwise it might be a little trickier if you don't want to do that...

But so what I did was:

1) create a straight table chart

2) add DateTime field as a Dimension

3) add this expression:

  • =only({< DateTime = {"=aggr(rank(DateTime),Date,DateTime)<=2"} >} DateTime)

4) in presentation tab of the object, hide your DateTime Dimension column 

 

Let me know if that works for you.

 

Thanks,

Ben

Brett_Bleess
Former Employee
Former Employee

You have two answers to your post, we would greatly appreciate it if you would return to the thread and close things out by using the Accept as Solution button on the post(s) that helped you get a working solution to your use case.  If you did something different, you can post what you did and use the button to mark that post as the solution too.  If you require further assistance, please leave an update for us.

Marking things gives the posters credit for the assistance and it lets other Members know what worked, that is why we request you please mark the post(s) that worked...

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.