Getting firstsortedvalue from spreadsheet with multiple entries per date

[Anonymous]

Hi, 

I hope someone will be able to help me, I have been struggeling and googling for a couple of weeks on and off. And I am quite sure it should be possible to fix my issue - I just don't know how.

I have a spreadsheet of multiple dates and data entries - see attached image. Each date has several entires. I need to be able to display the latest date in the spreadsheet along with the summed value of each column (for instance ZBB 3 column what I need to display in a text box is "19-12-2018 00:00:00 ZBB 3 = 1" )

I have tried practically all combinations of aggr and sum (except for the right one) 🙂

Can anyone please help?

BR

[sunny_talwar]

Is ZBB 3  a field or is it an expression?

[Anonymous]

it is a field

[sunny_talwar]

Try this

FirstSortedValue({<[ZBB 3] = {"*"}>} [ZBB 3], -Date)

[Anonymous]

As suggested I tried this:

FirstSortedValue({<[@1.ZBB 3] = {*}>} [@1.ZBB 3],-[@1.Date])

Then I thought maybe the date is seen as a string and tried:

FirstSortedValue({<[@1.ZBB 3] = {*}>} [@1.ZBB 3],-Date(Date#([@1.Date],'DD-MM-YYYY  hh:mm:tt')))

But none of these ends of in my textbox, it is just blank

[sunny_talwar]

How about if you add DISTINCT

FirstSortedValue(DISTINCT {<[@1.ZBB 3] = {*}>} [@1.ZBB 3],-[@1.Date])

[ptl14495]

Hi,

If the last date has empty results, we will get last date with null results.

Can you retrieve the results on the last data with results diferent from zero?

thank you