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

[drohm002]

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.

[benvatvandata]

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

[drohm002]

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]

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 · Accepted solution]

So like this?

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]

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