topic Re: How to Min date from multiple dates from Date field in App Development

How to Min date from multiple dates from Date field

SheetalB — Fri, 04 Feb 2022 09:01:25 GMT

Hi,

I am implementing logic to show min job card date time data only.

please find below snapshot of data set I am using.

Desired output should be

I have tried firstsortedvalue, Min funtion. But its not working

Kindly help..

Thanks

Sheetal

Re: How to Min date from multiple dates from Date field

hic — Fri, 04 Feb 2022 10:04:30 GMT

Min([Job Card Date Time]) should work just fine.

But the question is: What is your dimension - the grouping field? What field(s) do you want to use to define the number of rows in the output table?

Re: How to Min date from multiple dates from Date field

Andrei_Cusnir — Fri, 04 Feb 2022 10:15:00 GMT

I agree with @hic 's answer.

To add more context to that answer, here are the steps that I have followed to get your desired outcome:

1. The dataset:

2. Added all the dimensions to the table except the last one

3. For the JobCardDateTime I have used the dimension expression: =If(JobCardDateTime = Min(TOTAL JobCardDateTime), Date(JobCardDateTime, 'DD/MM/YYYY h:mm:ss[.fff]'), Null())

4. This expression checks if the current datetime is equals to the Min() datetime from the entire dataset. In the event that it is equal, return the datetime value, otherwise return Null().

5. Don't forget to un-check the "Include null values" under Data > Columns > JobCardDateTime (dimension)

6. The output is:

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, please mark it as accepted solution to give further visibility to other community members.

Re: How to Min date from multiple dates from Date field

SheetalB — Fri, 04 Feb 2022 11:09:19 GMT

Hello Henric,

I want to show all fields from the snapshot as below. The logic is job card date time should be immediate date after missed call date and time . Here missed call date is 29th Dec 21 and immediate date is 30th Dec 21. That's why I want show this in my report.

Re: How to Min date from multiple dates from Date field

hic — Fri, 04 Feb 2022 11:48:42 GMT

It's still not clear to me what you want to do.

If you want to filter out the "correct" records, then you should probably use a "where"-clause already in the script, e.g.
... where Ceil([Missed Call Date Time]) = Floor([Job Card Date Time])

Then you will only get the records where [Missed Call Date Time] is during the day before [Job Card Date Time].

But if you want to display the data in a chart, e.g. a pivot table in the UI, you first must decide what your dimension is. You will get one row per dimensional value. So it could be that your dimension is
* The call enter
* An issue number
* The record itself. Then you should create a RecordID in the script, e.g. RowNo() as RecordID

Once that is done, you can start adding your fields, and you can have exactly the ones you have in the screenshot. Add them as measures (not as dimensions) so you don't change the number of records in the table.

One measure could be
If(Ceil(Max([Missed Call Date Time])) = Floor(Min([Job Card Date Time]),'Correct','Incorrect')
so you get a flag for this condition.

Note that Min() and Max() are calculated only within the relevant dimensional value. That's why it is important to decide the dimension first.