How to show latest data by area

[Anonymous]

Hello,

I have data uploaded by clients daily showing RAG status. Data entered is not always complete therefore I would like to build a straight table showing the latest submitted RAG status by area. Can you please help how I can do this at the front end.

Here is the data I have at the moment

Area	Date and Time	Data
B	5/2/2018 10:47:27	Amber
C	5/2/2018 10:47:27	Red
D	5/2/2018 10:47:27	Amber
A	5/3/2018 10:40:00	Black
C	5/3/2018 10:40:00	Black
E	5/3/2018 10:47:00	Green
A	5/4/2018 12:27:05	Amber
E	5/4/2018 12:27:05	Green
B	5/4/2018 12:27:05	Black

And this is what I would like to Display

Area	Date and Time	Data
A	5/4/2018 12:27:05	Amber
B	5/4/2018 12:27:05	Black
C	5/3/2018 10:40:00	Black
D	5/2/2018 10:47:27	Amber
E	5/4/2018 12:27:05	Green

[Anonymous]

Thanks Arjun! As you've suggested I have amended the load script to

Load Date#(Timestamp, 'MM/DD/YYYY hh:mm:ss') as 'Date and Time',

This seem to do the trick. I was bit confused as in the row data 'date and time' is labeled as 'Timestamp'. I have then used what Sunny has suggested and also what you have suggested but I am getting data for the latest 'Date and Time' not the latest data for each area. Can you suggest anything else?

[Anonymous]

Thanks Vipin! this has worked as well without changing the load script but I am still getting Data with latest date. What I am looking to show is the latest reported data for each area. Any suggestion?

[Anonymous]

Hi Arjun,

Just replied back above. I am still struggling in showing the latest reported data only for each area.

[sunny_talwar]

Use this as an expression

TimeStamp(Max([Date and Time]))

[Anonymous]

Thanks Sunny! I am still getting the same result as above. It seems like it is picking up empty "Data" values from the table for the latest "Date and Time". Is there away I can put a condition to show latest data by area only for data with values.

Thanks

[sunny_talwar]

What exactly is wrong? I mean what exactly are you looking to get?

[Anonymous]

I want to show latest Data by area. So as you can see above on the screen shot I have the latest data for some areas but not for all. It is picking up data for the latest submission Date and Time. You see each submission by client can be different containing data for certain locations only, as some areas might not report daily. So what I want to show is data for each area with their last submission Date and Time.

Thx

[sunny_talwar]

Make sense... can you tell me what all dimensions and expressions are using?

[Anonymous]

I am using Area as dimension and the two expressions as attached above, Data and Date and Time range. I also have other dimensions as "Provider" and "Authority" that I am not using in this table.

[sunny_talwar]

Can you have multiple data at the same time? Try this

FirstSortedValue(DISTINCT Data, -[Date and Time])